Opening an Access Database from VB 1

[LisetteR]

Can anyone tell me why this doesn't work? I am new to VB but it seems to me that this should just work! It comes up with run time error 2486.


Private Sub Command1_Click()
Dim dbMyDB As Database

Set dbMyDB = OpenDatabase("C:\TideWeather.mdb")

DoCmd.OpenForm ("Start Sending")

End Sub

 

[zemp]

What and where is your error? Are you using Access VBA or VB? If Access VBA then and Access forum might be better.

faq222-1527 might be of help.

zemp

 

[LisetteR]

Im writing it in VB and producing an executable that checks that my database is updating a text file. If it hasn't updated for 20 minutes I want it to automatically close the access database and then re-open it.

As a start I have been trying to open the database. It opens access but hangs up on the DoCmd.OpenForm ("Start Sending") line.

Lisette

 

[johnwm]

As DoCmd is a specific to Access you need to reference the Access object in your DoCmd statement. If your Access object is called objAccess:

objAccess.DoCmd.OpenForm("StartSending")

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[LisetteR]

Thank you for your reply. Ok, I now have:

Private Sub Command1_Click()
Dim dbMyDB As Database
Dim rsMyRS As Recordset
Dim objAccess As Access.Application
Dim Application As String
Application = "C:\Program Files\Office97\Office\MSACCESS.EXE"
Set dbMyDB = OpenDatabase("C:\TideWeather.mdb")
objAccess.DoCmd.OpenForm ("StartSending")
End Sub

I'm getting "Run time error 91 Object or with Block variable not set". Debug shows the last line as being at fault.

I am using Access 97 and VB6, I'm not sure if I have the right references selected. As a beginner I'm a bit confused as to which I should be using. Would this make a difference?

 

[garths2]

Try puting "New" in the line where you are dimming objAccess. As in:

Dim objAccess As New Access.Application

GS

 

[LisetteR]

Thank you. That stops the Run time error 91.

This is the latest code I'm trying.

Private Sub Command1_Click()
Dim dbMyDB As Database
Dim rsMyRS As Recordset
Dim objAccess As New Access.Application
Dim Application As String
DBEngine.SystemDB = "C:\WINNT\SYSTEM32\system.mdw"
Application = "C:\Program Files\Office97\Office\MSACCESS.EXE"
Set dbMyDB = OpenDatabase("C:\TideWeather.mdb")
Set rsMyRS = dbMyDB.OpenRecordset("QWeatherTide", dbOpenDynaset)
objAccess.DoCmd.OpenForm ("StartSending")
End Sub


It now gives me a run time error on the last line again. This time it is:

Run time error 2486
You can't carry out this action at the present time. You tried to run a macro or used the DoCmd object in Visual Basic to carry out an action. However, Microsoft Access is performing another activity that prevents this action from being carried out now.

Cheers

Lisette

 

[garths2]

Try opening the Access database using the Access automation object:

objAccess.OpenCurrentDatabase "C:\TideWeather.mdb"

instead of doing
Set dbMyDB = OpenDatabase("C:\TideWeather.mdb")

It looks like you have a few unused variables like: "Application", "rsMyRS" and dbMyDB. You may want to consider deleting them.

GS

 

[garths2]

Open the database using the Access automation object instead of using the Database object, like this:

objAccess.OpenCurrentDatabase "C:\TideWeather.mdb"

instead of:
Set dbMyDB = OpenDatabase("C:\TideWeather.mdb")

GS

 

[LisetteR]

Hi,

I worked and then it stopped!

This is what I have now, there are no errors and Access starts up and the form opens then Access closes itself with no error codes.


Private Sub Command1_Click()

Dim objAccess As New Access.Application
Dim Application As String
Application = "C:\Program Files\Office97\Office\MSACCESS.EXE"

objAccess.OpenCurrentDatabase "C:\TideWeather.mdb"

objAccess.DoCmd.OpenForm ("Start Sending")

End Sub

 

[garths2]

Lisette,

You may try moving the Dim statement to the beginning of your Form source code, because it looks like you still want Access open after the button has been pressed. Right now you have objAccess local to the scope of the subroutine. VB may be destroying the object after the subroutine has been completed. You should also look at the macro "Start Sending" and make sure it does not contain errors.

By the way, when moving the "Dim" statement to the beginning of your Form make sure to change "Dim" to "Private". Like this:

Dim objAccess As New Access.Application

to:

Private objAccess As New Access.Application


In the Unload event of your Form make sure to close the Database and destroy the object as in:

objAccess.CloseCurrentDatabase
Set objAccess = Nothing


GS