Learn how to a build a cloud-first strategyRegister Now


VB6 Change code from Access to SQL

Posted on 2011-10-10
Medium Priority
Last Modified: 2012-05-12
I started running into size issues with Access so my data now lives on SQL Server 2008.  I'm still using the Access db, but instead of the table living in Access, it's linked to SQL Server.  This way, all my queries still work.

The problem, is the attached code worked before and does not now.  I get 'Object is not suported' on the Rst.Index.

Any ideas?

Dim Dbs As Database
    Dim Rst As Recordset
    Set Dbs = OpenDatabase("\\GOLD-HP\faster\db.mdb")
    Set Rst = Dbs.OpenRecordset("photos")
    Rst.Index = "PrimaryKey"
    Rst.Seek "=", value_picture_id
    If Rst.NoMatch Then
        'MsgBox "Not found!"
        'MsgBox "Found!"

Open in new window

Question by:hrolsons

Expert Comment

ID: 36947055
why don't you connect to the sql database instead?
LVL 16

Expert Comment

ID: 36950741
I know when we had to go from using an Access DB to SQL, our biggest issue was that the designer of the Access DB used spaces and SQL reserved words in the field names.

I recall we tried to link to the SQL server the way you describe, but the access was too slow.

I finally had to go back and write some logic that would allow me to access either database (and it was a pain).

Sorry, not any help, but you might just have to make code modifications to work with SQL.  The logic of your queries shouldn't need to be modified, so with some time, you should be able to make the needed changes.

Author Comment

ID: 36951347
I think you're both right, that it's time to bite the bullet and skip the middle-man, which in this case is Access.  This is going to be quite a project, as thousands of lines of code will need to be changed.

Just so I know I'm headed in the right direction, would it be correct to say that I was using DAO and need to start using ADO in order to connect directly to the sql db?
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 16

Expert Comment

ID: 36951633
Yes, DAO to ADO.

I went from using Microsoft DAO 3.6 Object Library to Microsoft ActiveX Data Objects 2.8 Library.  (That was a long time ago, a more up-to-date ADO interface might exist).

Since you've been working with DAO, the following code snippet might help get you started.  It was a part of an OpenRecordSet() function I had written that included a parameter to specify the type of recordset I wanted to use.  The code includes enums and variable names specific to my application, however, the names should be plain enough to understand how I had to setup record set properties in ADO to best match what I used to do in DAO.  

Select Case rsType
    Case RecordSetType_Dynaset
        ADO_RS.CursorLocation = adUseClient     'Required for RecordCount and AbsolutePosition Properties to work
        Call ADO_RS.Open(SQL$, m_DB_SqlSvr, adOpenKeyset, adLockOptimistic)
    Case RecordSetType_ReadOnly
        ADO_RS.CursorLocation = adUseClient     'Required for RecordCount and AbsolutePosition Properties to work
        Call ADO_RS.Open(SQL$, m_DB_SqlSvr, adOpenStatic, adLockReadOnly)
    Case RecordSetType_ForwardOnly
        ADO_RS.CursorLocation = adUseClient     'Required for RecordCount and AbsolutePosition Properties to work
        Call ADO_RS.Open(SQL$, m_DB_SqlSvr, adOpenForwardOnly, adLockReadOnly)
    Case RecordSetType_AppendOnly
        ADO_RS.CursorLocation = adUseServer
        Call ADO_RS.Open(SQL$, m_DB_SqlSvr, adOpenKeyset, adLockOptimistic)
    Case RecordSetType_TableType
        ADO_RS.CursorLocation = adUseServer
        Call ADO_RS.Open(SQL$, m_DB_SqlSvr, adOpenKeyset, adLockOptimistic, adCmdTable)
    Case Else
        ERROR_Raise ERROR_ProgrammerError, Source, "[" & rsType & "] is an invalid RecordSetType"
End Select

The biggest issue you might face going to ADO is whether or not you must rename any of your fields because ACCESS allows the use of SQL reserved words for field and table names.  If you should encounter this situation, I might be able to give you some hints on what I was able to do.

Accepted Solution

clamps earned 2000 total points
ID: 36953870
You don't have to change everything at once, since you can easily replace only parts of Access with MSSQL since you use Access as a "middleman".
Just start with the parts you need done right now and the transition is actualy quite easy...
And the change from Access to SQL wouldn't be an issue at all if you had started using ADO from the beginning :-)
Anyway, here's how you get started with ADO:
You need to link the Microsoft ActiveX Data Objects 2.x Library to your vba. (I use v2.8)

Dim Cn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim myHost As String
Dim myDB As String
Dim myUser as String
Dim myPass as String

myHost = "..." (replace with IP of the SQL Server)
myDB = "..." (replace with the Databasename you want to connect to)
myUser = "..." (replace with the UserID for SQL)
myPass = "..." (replace with the Password for SQL)

With Cn
  .ConnectionString = "Provider=sqloledb;Data Source=" & myHost & ";Initial Catalog=" & myDB & ";User ID=" & myUser & ";Password=" & myPass
  .Mode = adModeReadWrite
End With

With Rs
  .ActiveConnection = Cn
  .CursorType = adOpenStatic
  .LockType = adLockPessimistic
  .CursorLocation = adUseClient
  .Source = "SELECT * FROM whatevertable WHERE whateverfield=whatevervalue"
End With

'Do your stuff here
'Do your stuff here
'Do your stuff here



To work on your Data do stuff like:
Rs.AddNew (speaks for itself)
Rs.Fields("Fieldname").Value = 1
Rs.Update (don't forget the Update since you can't add another new Record without having updated the last one)

If Rs.RecordCount > 0 Then
  Do While Not Rs.EOF
End If
Is often used by me to run through all results to populate excel cells for example


In the parameters you have "Mode", "CursorType", "LockType" and "CursorLocation" you might want to read something about.
If I remember the CursorLocation = adUseServer won't let you use RecordCount on MSSQL so use adUseClient instead.
Modes I use are adModeReadWrite and adModeRead for better speed
LVL 46

Expert Comment

ID: 36955976
A simpler solution would be to attach the SQL table to the Access database.  That would make it visible to your VB code and shouldn't require any other code changes.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question