VB6 Change code from Access to SQL

Posted on 2011-10-10
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
    LVL 3

    Expert Comment

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

    Expert Comment

    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

    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?
    LVL 16

    Expert Comment

    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.
    LVL 3

    Accepted Solution

    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 44

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now