6

I am trying to rebuild an Access adp project in Access 2013 as and mdb. The tables are all linked tables in both version so that is not an issue.

I have imported the forms from the old project so the form layouts and code is all there.

Where I run into problems is when trying to execute the following code:

Dim cmd As New ADODB.Connection, RS As New ADODB.Recordset
cmd.ActiveConnection = connectionString
Debug.Print connectionString
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_Myproc"
cmd.Parameters.Refresh
cmd(1) = Me.my_id
Set RS = cmd.Execute

'Should be checking if record set is open and explicitly close it. JWS
If RS.State = 1 Then
RS.Close
Set RS = Nothing
End If

cmd.ActiveConnection.Close

I am not able to declare the cmd and RS variables because ADODB doesn't seem to exist. How do I reference this in Access or what is the correct way to accomplish this?

John S
  • 7,909
  • 21
  • 77
  • 145
  • 3
    In the VBA Editor, have you verified that the ADODB reference is enabled? (menu "Tools" > "References...", and verify that "Microsoft ActiveX Data Objects x.x Library" is checked). Further reference [here](http://msdn.microsoft.com/en-us/library/windows/desktop/ms677497%28v=vs.85%29.aspx). – Barranka Apr 03 '14 at 17:57
  • Thanks. I normally live in Visual Studio so am a little out of my element in the VBA editor. – John S Apr 03 '14 at 18:01

1 Answers1

10

As I wrote in my comment, you need to check that the ADODB reference is enabled:

  1. On the VBA Editor, clic on the "Tools" menu, and then clic on "References..."
  2. Verify thet the checkmark for "Microsoft ActiveX Data Objects x.x Library" is activated; if it is not, activate it.

Further reference: Using ADO with Microsoft VB & VBA

Barranka
  • 20,547
  • 13
  • 65
  • 83
  • Then the question becomes should I really be using ADO or is there a better preferred method? – John S Apr 03 '14 at 18:17
  • @JohnS The alternative I can think of is using DAO... but it is older. I use it and I don't have any problems, but it's up to you to decide which is more convenient for you ;) If you already have the code written using ADO, then it is easier to simply enable it. – Barranka Apr 03 '14 at 18:25
  • FYI I stuck with ADO so that they can get a few more years before a technology is phased out. Thanks for your assistance. – John S Apr 03 '14 at 18:37