OLE DB vs ODBC

[Nancy2]

Is there a way (or addin) where we can take our existing Access DB linked through ODBC to SQL by using OLE DB with limited programming and query changes? And, the users need to see the linked tables - like they do with the ODBC connection.

 

[JerryKlmns]

Maybe this help thread181-1399612
Also scroll down to this

http://www.oblique.ch/ms/ADOX_Faq.html

that says ODBC is a must.

 

[cmmrfrds]

Why don't you want to use ODBC to link the tables.

 

[BobRodes]

Jerry, I've done a little reading here, and even worked on setting up some linked tables in Access. My Access chops aren't that strong, so that's taking me some time going down blind alleys. But anyway, it's hard for me to accept that OLE DB doesn't support linked tables. Some of the reading I've done suggests otherwise, too, but I don't have a working example.

As for not wanting to use ODBC to link tables, I generally prefer to use ADO over DAO, and therefore would have to use the OLE DB provider for ODBC. I prefer to avoid doing that if I can, since it adds another layer to the solution.

Bob

 

[JerryKlmns]

Bob,

I tried linking an SQL Server 2005 table, but without the ODBC keyword in the .Properties("Jet OLEDB:Link Provider String"), there was the error message "80004005 - Could not find installable ISAM". I haven't done it before 'cause there was no need for that before. I 'm using VB6 for the UI to connect to SQL Servers.

Found this also thread705-1081913 where cmmrfrds does include the ODBC keyword.

 

[BobRodes]

I'm sorry, I can't really test this stuff well. What do you think about the code in here?

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarofftalk/html/office10052000.asp

 

[JerryKlmns]

Bob, no luck till now, with your link to use a DSN for an SQL Server. Maybe there could be some1 in forum705 knowing a secret about it. Maybe you should open a post there, just out of curiosity.

 

[BobRodes]

I'll do that.

 

[JerryKlmns]

Bob

Maybe you should open a post there, just out of curiosity.

should read

Maybe we should open a post there, just out of curiosity.

So embarassed about that. I 'm terribly sorry!

 

[BobRodes]

No problem. I did open that post, by the way. thread705-1402993

 

[JoeAtWork]

I'm a little confused as to what the OP's requirements are. It sounds like she wants to set up linked tables in Access to use OLE DB to maintain the link. I very much doubt it is possible to dictate to Access how it maintains its link (I also don't see the reason you would need to).

I'm not an authority on this, but I would guess that all of Access's internal code that deals with linked tables, is expecting the link to be through ODBC. So I doubt that Access has been designed to have two linking modes, e.g. one for ODBC, one for OLE DB.

Reading between the lines, I think the OP just wants to be able to easily work with the tables directly through ADO. I think her goals are:
1. Change the links to point to another database
2. Retrieve the ADO connection string that would be appropriate for one of the linked tables

For the first, I have the following subroutine:

'Created:       17-July-2007
'Version:       2.0.2
'Purpose:       Changes the ODBC connection string for all SQL Server linked tables
Public Sub ChangeSQLConnection(NewConnection As String)
    Dim dbs As DAO.Database
    Dim tdf As TableDef
    
    Set dbs = CurrentDb
    
    For Each tdf In dbs.TableDefs
        'Only change tables linked to SQL server
        If Left(tdf.SourceTableName, 4) = "dbo." And Left(tdf.Name, 4) <> "MSys" And _
         tdf.Connect <> "" Then
            tdf.Connect = NewConnection
            tdf.RefreshLink
        End If
    Next
    
    Set tdf = Nothing
    Set dbs = Nothing
    
    MsgBox "Finished Relinking!", vbInformation, "Finished"
End Sub

The NewConnection parameter would be a DSN-less ODBC connection string, something like:

Provider=SQLNCLI;Server=MyServerName;Database=MyDBName;Uid=SomeLogin;Pwd=TheSecretPassword;

For the second presumed requirement, I have created a function that can give me the ADO connection string equivalent of an ODBC linked table.

Public Function GetSQL2005ConnString() As String
'Version:       2.0.0
'Date Created:  27-Jun-2007
'Purpose:       Builds the ADO connection string to the SQL 2005 database by
'               parsing the ODBC connection of one of the linked tables
    Dim odbcConn As String
    Dim server As String
    Dim uid As String
    Dim pswd As String
    Dim db As String
    Dim start As Integer
    Dim endAt As Integer
    
    odbcConn = CurrentDb.TableDefs("Employees").Connect
    
    start = InStr(1, odbcConn, "SERVER=") + 7
    endAt = InStr(start, odbcConn, ";")
    server = Mid(odbcConn, start, endAt - start)
    
    start = InStr(1, odbcConn, "DATABASE=") + 9
    db = Mid(odbcConn, start)
    
    start = InStr(1, odbcConn, "UID=") + 4
    endAt = InStr(start, odbcConn, ";")
    uid = Mid(odbcConn, start, endAt - start)
    
    start = InStr(1, odbcConn, "PWD=") + 4
    endAt = InStr(start, odbcConn, ";")
    pswd = Mid(odbcConn, start, endAt - start)
    
    GetSQL2005ConnString = "Provider=SQLNCLI;Server=" & server & _
     ";Database=" & db & ";Uid=" & uid & ";Pwd=" & pswd & ";"
     
End Function

These two functions allow me to fairly easily write ADO code in a database that has tables linked with ODBC.

 

[BobRodes]

< It sounds like she wants to set up linked tables in Access to use OLE DB to maintain the link.

I'm not seeing that personally. What I'm thinking is that an OLE DB provider simply accesses a data store and makes the data available via the OLE DB provider interface. So, not to maintain the link per se, but that an Access file, linked tables and all, is the data store that an OLE DB provider accesses to provide the data. Whether Access is linking in a table (whether using ODBC or not) or not ought to be transparent to the OLE DB provider.

 

[JoeAtWork]

the users need to see the linked tables - like they do with the ODBC connection

That's where I get the idea that she wants to change how Access links to the tables. But her post is rather vague and requires elaboration.

Nancy, where did you go?

 

[BobRodes]

Well, maybe so....