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

VB6 Change code from Access to SQL

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!"
    Else
        'MsgBox "Found!"
    EndIf

Open in new window

0
hrolsons
Asked:
hrolsons
1 Solution
 
clampsCommented:
why don't you connect to the sql database instead?
0
 
HooKooDooKuCommented:
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.
0
 
hrolsonsAuthor Commented:
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?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
HooKooDooKuCommented:
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.
0
 
clampsCommented:
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
  .Open
End With

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

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

Rs.Close
Cn.Close

########################################################

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
    ...
    Rs.MoveNext
  Loop
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
0
 
aikimarkCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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