CODEWITHSUNDEEP
ms-accessmodulevba
user2119980
user2119980

Reputation: 509

AutoExec Module not Initiating

My data storage form has a new module (AutoExec) which is meant to test the connection to a SQL server db upon opening the form. Only it is not firing when opened. I was wondering if there was something in my code causing this, this is my first Module so I am not familiar with the proper forms yet.

Public Sub AutoExec()
Dim cnn As ADODB.Connection
Dim localrst As New ADODB.Recordset
Dim remoterst As New ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB; Data Source=DB\P003,49503; Initial Catalog=HRLearnDev;" _
    & "User Id=USERNAME; Password=PASSWORD;"

If cnn.State = adStateOpen Then

    MsgBox ("You have an established connection.")
  Else
    MsgBox ("Cannot connect to remote server. Data will be stored locally  to CDData Table until application is opened again.")

End If

cnn.Close
Dim rst As New ADODB.Recordset


End Sub

Upvotes: 2

Views: 9459

Answers (1)

HansUp
HansUp

Reputation: 97131

When a database includes a macro named AutoExec, Access will run that macro at database startup.

In order for that to work, AutoExec must be an Access macro object. In your case, you have a VBA procedure named AutoExec. Since that is not a macro object, Access does not run the procedure automatically at database startup.

I suggest you create a VBA function ...

Public Function Startup()

Add the code from the body of your AutoExec procedure to the function.

Then create a new Access macro (an actual Access macro object --- on the ribbon in Access 2007, choose Create->Macro), use the macro RunCode action to run your new Startup() function. Name that macro object as AutoExec.

Here is a screenshot of my simple AutoExec example macro open in Design View.

AutoExec macro in design view

Upvotes: 7

Related Questions