Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    81

    Unanswered: VBA to link tables to SQL?

    I use code to attach frontend MS Access 2000 .mdb tables to .mdb backends. It works great. Now I'm switching over to MS SQL Server tables on all backends, connecting the tables through ODBC to a SQL database.

    How to use VBA to attach to SQL?

    Bjorn


    Here is the code for .mbd backends:
    -----------------------------------------------------------------------
    Private Sub cmdRelinkLocally_Click()
    If MsgBox("Go Local?", vbYesNo, "Relink Tables Locally?") = vbYes Then

    Call UpdateLocalBackends() 'Fctn updates local .mdb with network data

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strPath As String
    Dim strPath2 As String
    'Two different backends
    strPath = "C:\Backend.mdb"
    strPath2 = "C:\Backend2.mdb"
    Set db = CurrentDb()

    For Each tdf In db.TableDefs
    If (tdf.Attributes And dbAttachedTable) = _
    dbAttachedTable Then
    If tdf.Name = "tblSomeTable" Or tdf.Name = "tblOtherTable" Then
    tdf.Connect = ";DATABASE=" & strPath2
    Else 'All other attached tables
    tdf.Connect = ";DATABASE=" & strPath
    End If
    tdf.RefreshLink
    End If
    Next
    MsgBox "You are local! Any data changes will be lost."
    End If
    End Sub
    -----------------------------------------------------------------------

  2. #2
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    it's pretty much same. but for linked table connection, u need have dsn and database name.

    dsn is datasource u created with sql utility.

    for example, u change code here
    .connect = "DSN=xxx;DATABASE=xxx;userID=xxx;password=xxx"

  3. #3
    Join Date
    Jan 2003
    Posts
    81
    OK. But what do I do with

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef

    ???

    Bjorn

  4. #4
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    yes.
    then
    set db = currentDB
    Set tdf = db.TableDefs(tableName)
    tdf.Connect = "DSN=xxx;DATABASE=XX;UID=xxx;PWD=xxx"
    tdf.SourceTableName = "sourceTableName"
    tdf.RefreshLink

  5. #5
    Join Date
    Jan 2003
    Posts
    81
    Great. Thanks!

    Bjorn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •