• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

Converting ODBCDirect recordset insert to ADO for Access 2007

I've got a function that inserts a new row to a linked table (Sybase ASE database), running in Access 2003.  Since we're migrating to Access 2007 and ODBCDirect is no longer supported, I'm trying to rewrite this using ADO.  I'm completely stuck.  My code is below, can anyone get me moving in the right direction?  Thanks!

   Dim db                     As Database
   Dim recOut                 As Recordset
   Set db = CurrentDb
   Dim strSQL                   As String
   Dim strQueryName             As String
   Dim lngUdtHistNo             As Long
   Dim wrkOdbcDirect            As Workspace
   Dim cnx                      As Connection

   'Write out the history record.
   Set recOut = db.OpenRecordset("dbo_udt_hist", dbOpenDynaset, dbAppendOnly)
   recOut!udt_hist_no = GetItn("udt_hist_no")
   recOut!udt_hist_dtime = GetServerDTime()
   recOut!user_name = gobjEnvironment.LogonUserName
   recOut!udt_obj_form_name = mfrmCurrentForm.Name
   recOut!intrn_key_val = mvarIntrnKeyVal
   If Len(mvarHistText) <= 250 Then
      recOut!short_hist_text = mvarHistText
      recOut!full_hist_text = Null
      recOut!short_hist_text = Null
      recOut!full_hist_text = mvarHistText
   End If

  • 3
  • 2
  • 2
1 Solution
It seems a little odd that there's no driver to support an existing database format.  If you can recreate the linked table & add new records manually in that table you can still use DAO like in the code above.  Although personally I'd use:

With CurrentDb.OpenRecordset("SELECT * FROM LinkedTable;",[Arguments])
  'do stuff
End With

After all, it is a linked table.

However, if it really has gone Pete Tong then ADO is not a miillion miles away from DAO.  Create a connection object first, then a recordset like this:

Dim objConnection As ADODB.Connection
Dim objRecordset As ADODB.Recorsset
Dim strConnaction As String
Dim strSQL As String

'Create connection string
strConnection = "Database='' etc."

   Set objConnection = New ADODB.Connection
   objConnection.Open strConnection

    strSQL = "SELECT * FROM etc."
    Set objRecordset = New ADODB.Recordset
    With objRecordset
        Set .ActiveConnection = objConnection        
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .Open strSQL

        'do stuff
    End With

Your challenge, should you wish to accept it, is to bulld the right connection string!  This page will help:
L_MalchiodiAuthor Commented:
Thanks very much......I've made my changes and tried to compile, and I'm blowing up on the very first line:

Dim objConnection As ADODB.Connection

With a "user-defined type not defined:" message.  It doesn't seem to like ADODB.Connection.  Is there something I need to set up elsewhere?
Leigh PurvisDatabase DeveloperCommented:
Can I just ask first...
Is there some code missing which you're using compared to that which you posted?
You declare a Workspace and Connection object (as if you were about to use ODBCDirect) but make no use of them.
Is this your post ODBCDirect code attempts?
As for your issue - I dare say that OxonDev had just assumed that, since you're wanting to move to ADO, you'd have already added a reference to it.
There's not a lot of reason to go for a newer version, in 2007 I think about 2.5 would be the default, but 2.8 (technically the last version) is perfectly available.
You're looking to add a reference to:
Microsoft ActiveX Data Objects 2.x Library
depending upon the version you choose.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LPurvis is right.  I should have said that you need a reference to Microsoft ActiveX Data Objects 2.x Library.  Very sorry.
L_MalchiodiAuthor Commented:
OK, that's a bit better since I added the Microsoft ActiveX Data Objects 2.8 Library.  Now it looks like my connection string is bad, since I got this message:

"Data Source name not found and no default driver specified."

Using the site that OxonDev provided (thanks), I came up with this connection string for a Sybase ASE 15.5 database:

strConnection = "Driver==(Adaptive Server Enterprise);server=blinky;port=10000;db=PRD_LABDATA;uid=PRD_LABDATA;pwd=PRD_LABDATA"

As you suspected, I know next to nothing about ADO, or ODBCDirect, for that matter.  I maintain a very large, complex Access db that was written by someone else about 10 years ago.  I've changed plenty of code, but have never had to deal with the connection 'plumbing'.

Thanks again for the help,
Leigh PurvisDatabase DeveloperCommented:
That's no problem. But, given that the code you posted doesn't actually make any use of ODBCDirect, then the tables referenced must have been local (or a query accessing external objects).
The naming cconvention of "dbo_udt_hist" very much implies that they're local linked tables.
In which case, you shouldn't need to change anything.
So, with that in mind, what's actually happened / gone wrong?
As for the connection string, it's hard to know without knowledge of the platform/version etc.
But, since you're moving to ADO, then you might as well shoot for the OLEDB provider.
Provider=ASEOLEDB;Data Source=blinky:5000;Initial Catalog=PRD_LABDATA;User Id=PRD_LABDATA;Password=PRD_LABDATA;
If it's installed on the client PC. If not then the ODBC provider must be (as you were using it previously - be it with linked tables or ODBCDirect).
That can be used with ADO too.
But I'd say your first port of call is identifying what, if anything, has stopped working.
Because only the ODBCDirect components will no longer work. The rest should be fine if it was previously.
L_MalchiodiAuthor Commented:
Sorry for the delay in getting back to this,  I got pulled onto another project.

LPurvis, I think you nailed it...the ODBDirect references in this code don't appear to be used for anything.  They seem to be remnants of an earlier coding project that were not removed from the code.  I commented them out, and am no longer getting the error.

Thanks very much for the help!

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now