Launching Access using Visual Basic [Archive]

View Full Version : Launching Access using Visual Basic

mazac

01-14-03, 06:15

I am using a little VB6 program that compares the versions between a local Access 2000 database and one on the server. If the server version is greater this file is copied locally and then MSAccess is launched. The problem that I am having is that I now need this to work on Windows XP but the path to MSaccess.exe is different to Win2000 so the shell command is failing.

In the short term I have fixed the problem by adding an 'If...Then' clause but I was wondering if there is some generic code that I can use in VB to locate the full path for MSAccess.exe so that I can shell to it from any OS.

In Access VBA I could have used SysCmd(acSysCmdAccessDir) so I'm hoping for a VB6 equivalent to this.

Thanks for all the help.

playernovis

01-18-03, 16:55

you can have installed Access97, Access2000 and AccessXP on one machine (as I do), so you should rather run MSACCESS.exe instead of pointing to directory....

try this:

DestinationFile = "C:\Temp\Database.mdb"
RunThis = "Msaccess.exe " & Chr(34) & DestinationFile & Chr(34)

Set WshShell = CreateObject("WScript.Shell")
WshShell.Run RunThis, 3, False

i used in SCRIPT

jiri

Ad Dieleman

01-19-03, 12:17

You might use the Access library by referencing the "Microsoft Access 9.0 Library" which should be visible if Office 2000 has been installed on the PC where you run VB6 on. On my PC it is 10.0 because I use Office XP.

You could then use a piece of code like this:

Sub Test()
Dim appAccess As Access.Application

Set appAccess = New Access.Application
appAccess.Visible = True
appAccess.OpenCurrentDatabase "D:\Ad\Werk\RBO.adp"

'Do your stuff by using the Access object-model

appAccess.Quit
End Sub

mazac

01-23-03, 04:34

Thanks for the response. I've used this WshShell object in Visual Basic scripting but I can't seem to get it to work using VB6. I tried your code but I keep getting an error:

Run-time error ‘-2147024894 (80070002)’:
Method ‘Run’ of object ‘IwshShell2’ failed

Am I forgetting something?

mazac

01-23-03, 04:38

Thanks Ad Dieleman. I tried this but get the error:

Run-time error ‘429’:
ActiveX component can’t create object

when it reaches the line:
Set appAccess = New Access.Application

I check the references and they seem to be fine.

Any ideas?

(P.S. Forgive my ignorance)

Ad Dieleman

01-23-03, 16:52

I never encountered this error with Access or any other Office-application. The only suggestion I can offer is to reinstall Office.

Good luck.

Ad Dieleman.