Open Access Report in VB 6

Lars213
10-27-2004, 08:26 AM
I have written a program in VB 6 that connects to an Access database. Everything is stored on a network drive at my work so that anyone on our network can access the program.

The problem I am having is that when I try to open a report on someone else's PC, that has been created in Access, by using the VB 6 program, I get the following error:

"Run-time error 7866

Microsoft Access can't open the database because it is missing, or it is opened exclusively by another user."

I have three instances that make this error frustrating to me. First, I have made sure that when I ran the program from the faulty PC that no one else had the database open or was running the program. So his PC would have been the only one with the database open. Second, when I run the program on my PC, all features, including opening all of the reports, run fine. This problem only seems to be on some of the machines, not all of them. Last, the program obviously connects to the database, because I put in a username/password sign-on form that has to validate information from a password table I created. Also, my program pulls other data from other tables and the program displays all of that fine on all of the PCs. It's opening the reports on some of the PCs that I am having problems with.

I tested the reports a second way by opening the Access database and ran the reports in Access, and they ran fine on the faulty PCs. So there is some sort of confusion between my program, the reports, and possibly some network security settings.

I will paste my code that I am using to open the reports with. Any help that anyone can provide is MUCH appreciated! Thank you!

Private Sub DeptBalances_Click()
Dim appAccessReport As Access.Application

Set appAccessReport = New Access.Application

appAccessReport.OpenCurrentDatabase ("J:\PTO\PTO.mdb")
appAccessReport.DoCmd.OpenReport "DepartmentBalances", acViewPreview
appAccessReport.DoCmd.Maximize
appAccessReport.Visible = True
End Sub

Dennis DVR
10-27-2004, 08:41 AM
is the drive j: mapped successfully? did you try to reference it by using UNC path? what is the OS of the PC where your program is working and what is not? is it just the report that cannot be opened or the user cannot even add or edit the records?

Shurik12
10-27-2004, 08:43 AM
Just a question, do those "faulty" machines have Access installed on them?

Lars213
10-27-2004, 09:05 AM
Here is the answers to the two previous responses:

1. The J: is mapped correctly on all of our PCs. There are other files/folders that everyone needs to access off of this drive. All PCs that have been tested are running WinXP Pro. As far as what people can do, all PCs can retrieve, add, edit, delete, and update records from the database. The error is in opening the reports. I'm sorry, I don't know what referencing by UNC is.

2. All of our PCs have Office XP Pro installed on them.

One other question. I don't know if this would have anything to do with it or not, since I can get it all to work fine on some PCs. Would the following statement have to be included since at the point when someone would try to open a report, there is already a connection to the database? Could this possibly be why the error is saying that it is already opened by another user?

appAccessReport.OpenCurrentDatabase ("J:\PTO\PTO.mdb")

This statement to me sort of sounds like it is trying to open the database again, even though the user signed in already has it open. Just curious....

Thanks for your help!

Lars213
10-27-2004, 09:09 AM
I just tried eliminating the string: appAccessReport.OpenCurrentDatabase ("J:\PTO\PTO.mdb") and tried to run a report on a PC that works properly and I got an error message. So I guess that idea didn't work....

Shurik12
10-27-2004, 09:14 AM
Hmm...
Could you make a test: save this PTO.mdb on the hard drive and try to run the code.
Will it work?

Lars213
10-27-2004, 10:13 AM
I copied all of the files from the network drive and copied them to a faulty PC's hard drive, and changed the paths from J: to C:, and all of the program's functions worked, including opening the reports. Good suggestion. Any ideas now? Thanks again!

Shurik12
10-27-2004, 10:34 AM
Bizzar...
You don't include "exclusive" argument in OpenCurrentDatabase so (by default) it can be in principle shared my different users.
Anyway to check further can you simulate "sumultaneuos" users on the hard drive and on the network?
Is there any difference?
Please make sure that .ldb files are deleted before you start testing(you might know that sometimes Access leaves that stuff behind even if the database is closed)

Lars213
10-27-2004, 12:22 PM
I tried this and can log into the program simultaneously, but the program that ran off of the network drive still could not open the reports. I also made sure that the .ldb file was not present.

I've had this problem for a while now, and I really appreciate your help with this. I just can't figure out what is wrong. Again, thanks!

noccy
10-28-2004, 04:48 AM
Maybe you could try to use the "network path" of the file: "\\Server\PTO\PTO.mdb" instead og using j:\?

Lars213
10-28-2004, 09:54 AM
I tried puting the network path instead of the J:, and it worked fine on my PC, but still would not work on the other PC I really need it to work on. Ugh!.... I appreciate your suggestion though, thanks!

I also made sure to check that he had all of his updates downloaded from Microsoft Office's update site. Any more ideas? Thanks!

Lars213
10-28-2004, 10:13 AM
Is my program opening the database exclusively? Is there code to tell it to open it as shared? I'm wondering if this might be a problem where my program is opening it exclusively instead of shared. But yet like I've said before, some PCs run the program fine and others don't. Just a thought.....thanks!

Shurik12
10-28-2004, 10:17 AM
>some PCs run the program fine and others don't.

Yet another question and not the answer... Are these the SAME PCs that cause the problem or is it something random?

Lars213
10-28-2004, 10:59 AM
I'm sorry, I'm not really sure as to what you mean by 'same PCs'. I'll try to explain our setup here. We have roughly 40 PCs on our network here at work. All of the PCs access the same network drives, so I can go to any PC here and access the program. As long as I put the file MSADODC.OCX in the directory C:\WINDOWS\system32 on all PCs, every one of them can open, run, display, add, edit, update, and delete entries from the database. When I go to run any of the reports, only a few of the PCs can actually run and open the reports. The PCs that fail all have the same run-time error 7866. I've also made sure that only one PC is running the program at a time when I run it.

As far as the PCs themselves, each are running either Windows 2000 or Windows XP Pro. Only 5 at most are still running Windows 2000. With that said, obviously all of the PCs were not purchased at the same time. Each computer has the same level of permissions/access on their username accounts, except for Administrators. I have checked PCs that have our normal permissions and ones with Administrative permissions, and have had some work and some not work for both normal and Administrative permissions. It is all confusing as to why one PC will open the reports and why another one won't.

I hope I answered your question and just didn't ramble on here. Sorry again for misunderstanding what you were asking. Thanks!

Shurik12
10-29-2004, 08:38 AM
Hi,

Pardon if I were not clear

>I'm sorry, I'm not really sure as to what you mean by 'same PCs'

If you have 40 PCs on the network (named A, B, C....). Is this true that say only 'A' and 'B' cause the trouble. Or is it so that one day you have problem with 'B' another with 'A'.
Having answered this question one can be able to narrow the search:
-if one and the same PC(s) can't run the report and the others well (given you have the same version of program installed on all of them) then there's something 'wrong' with that certain machines and not your prog.
(think about drivers, ocx registrations, etc...)


I think the first thing to check is whether ALL machines have the latest MDAC
+service pack installed.

Also do the machines causing trouble have W2K or XP only or it's platform independent
problem?

Lars213
10-29-2004, 10:32 AM
The problems are specific to the PC. If it doesn't work once, it never works on that particular PC, and vice versa. So it is not a random issue. Also, the problem appears to be OS-platform independent.

I will look for those service packs and see if that will work. I've gotten all of the Windows and Office updates, but didn't think about the MDAC service packs. Thanks for the suggestion and will let you know how it goes.

Thanks again for your help, it is greatly appreciated!

Lars213
10-29-2004, 11:02 AM
I tried downloading the most recent version of MDAC service pack (2.8), and XP said that it was already installed on the machine. That was the most recent version I could find. But, it didn't help either. I'm about to give up.... I'm gettin more and more frustrated with this.

I do appreciate your help through this, and if you have other ideas, please feel free to submit them. But I'm about to the point where I'm accepting that it just isn't going to work on everyone's PCs. Thanks again!

BarrySumpter
11-17-2004, 03:18 PM
Some suggestions:

Don't use the data control
write standard sub routines to
a) open the database - SHARED
error check for 'can't open database'
- try three times and get out

b) close the database
c) open a recordset
calls a, opens rs, calls b, and returns the recordset

Always open db, do your work, close db.
Never leave the db open.

Keep your testing environment under control.
Keep all your testing on your development PC until your sure you can run two executables successfully on the same pc at one time.
Once thats proven then attempt the test on a different single test machine.
ie. running on the test machine by itself - then running on the development machine and on the test machine at the same time.
Once thats proven select a testing group of 5 pcs/users who are interested in assisting. Let them have a fair bash for a week or a month.
Once thats proven then release to other users.
Once everyone is up and running make the official announcement.

Don't know why MS doesn't supply a proper MSAccess reporting interface for VB. MSAccess is the best and most popular db around.
Crystal is a nightmare and feeble attempt of the reporting in VB6 isn't worth the trouble.

We use .snp snapshots to run MSAccess report thru VB. Very fast.

The same technique applies.
Open the database, run the report to a .snp, close the database.
Open the .snp in the snapviewer.
It was fun sorting out the interface for the .snp viewer.

The biggest problem is again MS shooting themselves in the foot by not producing a proper interface for running MSAccess reports thru VB.

The problems being with the security features and popups from running a report in this manner. Script warnings. Can't change parameters, etc....

hth

Let me know how you go.


baz

Shurik12
11-17-2004, 03:32 PM
>MSAccess is the best and most popular db around.
Crystal is a nightmare and feeble attempt of the reporting in VB6 isn't worth the trouble.

Sorry Barry, (although it's an off-topic) but it's not completely true, to say the least. I really don't know what made you think so.

BarrySumpter
11-17-2004, 03:40 PM
Hi Steppe,

Just my opinion from years of experience.
Check my recent posts for reasons.

If you've got it working satisfactorily then I'm happy someones got it sorted out. And wish you all the best. And certanly support your opinion to disagree.

Anything over and above a simple flat file report is just a waste of time.
Again, just my opinion from years of experience.

baz

Shurik12
11-17-2004, 04:26 PM
No problem Barry, I presume that each product has its own niche and can suit one's needs.

Regards,
Shurik.

Lars213
11-22-2004, 03:51 PM
Thanks again for everyone's suggestions. I will include a .zip file of my project, and if anyone is interested, they can open it and run it themselves and see if they get the same problem I am having - opening up the reports.

You will need to place the folder in the .zip file directly on your C:, as the connection string for this is C:/PTO_Forum/PTO.mdb

The username and password to log into the program are both 'user' (without the ' ').

If you want to open my code and see the forms that I created, the following forms are the ones in use with this .zip file: frmEmployeeBalance, frmLogin, and frmPassword. The other five forms are for the main administrator of this program and are not used by the username and password that I have provided. The main form that is set up with this .zip file has a menu item called Reports, and this is where you can open the reports.

If anyone does decide to try this out, please try to open any or all the reports and let me know if you were successful. A huge 'Thank You!' in advance to anyone who tries to run my program and that provides any feedback.


removed the attachment

Lars213
11-22-2004, 05:12 PM
I forgot to add this on my last post. The file to run is called PTO.exe. Thanks!

VBJoe
11-22-2004, 08:29 PM
Executing the file PTO.exe, the third report in the list gave me the same error you're getting. However, running the application from within the VB IDE went perfectly, and once I recompiled the executable, I could run it with no problems occuring with any of the reports.

Don't know if that helps...

Shurik12
11-22-2004, 11:22 PM
Lars,

Sorry but I had to delete your attachment since no executables are allowed
(although I understand this the point of your question)

Thanks,
Shurik.

Lars213
11-23-2004, 09:02 AM
I'm sorry, I didn't know that executables were not allowed to be posted. It will not happen again. Thanks!

stephenlecompte
10-06-2005, 02:07 PM
I'm sorry if this was already stated but please try:

appAccessReport.OpenCurrentDatabase ("J:\PTO\PTO.mdb"), True

instead of

appAccessReport.OpenCurrentDatabase ("J:\PTO\PTO.mdb")

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum