How to link to MS SQL Server’s table in Access programmatically? 1

[sudakov]

Does anybody know how to create a link to MS SQL Server’s table in MS Access database programmatically?

sudakov

 

[GregTheGeek]

sudakov,

There are several different ways you can get to data in MySQL in Access via code. What it sounds like you want to do is to create a linked table directly to the table on the server.

Here's my code (roughly):

Sub LinkTable(ByVal myTableName As String, ByVal myDatabase As String)
    Dim daoTableDef As dao.TableDef
    Dim db As dao.Database
    Dim newTable As dao.TableDef
    Dim strConnect As String
    
    strConnect = getMySqlConnectionString(myDatabase, True)

    Set db = CurrentDb
    Set daoTableDef = db.CreateTableDef _
       (Name:=myTableName) 
    
    With daoTableDef
        .Connect = strConnect
        .SourceTableName = myTableName
    End With

    db.TableDefs.Append daoTableDef

    Set daoTableDef = Nothing
    Set db = Nothing

End Sub

Private Function getMySqlConnectionString(ByVal myDatabase _
    As String, ByVal blnLinkedTable As Boolean) As String

    Dim UserName As String
    Dim Password As String
    Dim ServerAddr As String
    Dim Driver As String
    Dim Port As String
    Dim strODBC As String
    
    ServerAddr = "192.168.1.1"
    
    Port = "3307"
    Driver = "{MySQL ODBC 3.51 Driver}"
    UserName = {Your User Name}
    Password = {Your Password}
    
    If blnLinkedTable Then
        strODBC = "ODBC;"
    Else
        strODBC = ""
    End If
    
    getMySqlConnectionString = strODBC & "Driver=" & Driver & ";" & _
        "Server=" & ServerAddr & ";" & _
        "Port=" & Port & ";" & _
        "Database=" & myDatabase & ";" & _
        "User=" & UserName & ";" & _
        "Password=" & Password & ";" & _
        "Option=3;"

End Function

HTH!

Greg The Geek

GTG

 

[GregTheGeek]

Oops, sorry, I just realized I misread your original post. :-(

I thought you wanted to get to mySQL data, not MS SQL. Really, all you need to do is change the getMySqlConnectionString function. Take a look at

http://www.connectionstrings.com/

for the connection string you think would best work.

Hope THIS helps!

Greg The Geek

GTG

 

[kjv1611]

GregTheGeek,

sudakov is looking for linking MS SQL, not My SQL. Big difference there.

sudakov,

Look in the help files for the context for TransferDatabasep

At least in Access 2007, the 2nd example given shows how to connect to an ODBC database (such as your MS SQL database).

--

"If to err is human, then I must be some kind of human!" -Me

 

[GregTheGeek]

kjv1611,

Not that big of a difference. As I stated in my correction, all he needs to do is change the connection string. Everything else should work fine.

GTG

 

[kjv1611]

Yeah, and I didn't refresh before I posted. Otherwise, I'd of seen your corrected post.

Also, the connectionstrings.com site is a VERY good site to remember when dealing with data connections. I know PHV's mentioned it many times in the past.

--

"If to err is human, then I must be some kind of human!" -Me

 

[sudakov]

GregTheGeek,

It works!!!
I did some adjustments in order to connect to MS SQL Server instead of MySQL.

Thank you very much!

kjv1611,
thank you for your comments as well!

sudakov