?
Solved

how to use VBA to connect to ODBC SQL Server

Posted on 2009-05-14
6
Medium Priority
?
851 Views
Last Modified: 2012-05-07
I have an MSAccess 2007 application that currently links to SQL Server.  The links were established using External Data, but I want to refresh those links depending on what SQL Server database our customer selects (production vs development).  I tried using docmd.transferdatabase but encountered an error regarding an "uninstallable ISAM file."  I've read that this is a known Microsoft bug.  Can someone provide me an alternative solution using VBA code.  Thank you!
0
Comment
Question by:CaroleTSullivan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24392640
Using a dsn or dsn-less (dsn-less is best)?


Kelvin
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24395480
This sample database provides automatic and manual linking to the backend.
If the front end does not find the backend, it will relink to a location you specify.
It also has a form to browse or search for the backend and manually change it.
When I am doing coding or troubleshooting, I link to a different copy of the backend to reduce the chance that I accidentally change the real data.
http://www.thenelson.name/ Link To Backend

Hope this helps.
Nelson
0
 

Author Comment

by:CaroleTSullivan
ID: 24398347
the current links use the MSAccess links, but I want to link via VBA code using DSN?

thenelson:  interestesting approach.  I'll need time to look at that, but our corporation doesn't allow us to download so can you include the VBA that checks the be links.  Or is there a way to switch between be.mdb files?  

thanks,

Carole
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 39

Expert Comment

by:thenelson
ID: 24398502
Here's the code in basLinkTable Module in the referenced db
Option Compare Database
Option Explicit
'If your backend is not in the same folder as the frontend, enter its full path in the next line
Const DefaultPath = ""
'If your backend is named other than the same name as your frontend & "_be.mdb, enter the name in the next line
Const DefaultBEName = ""
'enter the name of a table in your backend
Const DefaultTableName = ""
 
Public Sub RefreshLinks(Optional strPathName As String = "", _
    Optional strFileName As String = "")
' Refresh links to the supplied database.
 
    Dim dbs As Database
    Dim dbsLink As DAO.Database
    Dim tdf As TableDef
    Dim TableLength As Single, TableCount As Integer
    
    DoCmd.Hourglass True
    DoCmd.OpenForm "Link Back End"
    Forms![Link Back End]!lblLinkingWait.Visible = True
    Forms![Link Back End]!ProgressMeter.Visible = True
    Forms![Link Back End]!btnCancel.Visible = False
    Forms![Link Back End]!SamePath.Visible = False
    Forms![Link Back End]!SpecifiedPath.Visible = False
    Forms![Link Back End]!cmdSearch.Visible = False
    Forms![Link Back End]!cmdBrowse.Visible = False
    Forms![Link Back End]!lblPath.Caption = "Currently linking:"
    Forms![Link Back End]!LinkingFile = ""
    Forms![Link Back End]!lblFile.Caption = "Progress:"
    Forms![Link Back End]!MeterBackground = ""
 
    If strFileName = "" Then If Len(DefaultBEName) Then strFileName = DefaultBEName Else _
        strFileName = getfileroot(CurrentDb.Name) & "_be.mdb"
    If strPathName = "" Then If Len(DefaultPath) Then strPathName = DefaultPath Else _
        strPathName = getpath(CurrentDb.Name)
    If StrComp(Right(strPathName, 1), "\", vbTextCompare) <> 0 Then
        ' Append trailing backslash to path.
        strPathName = strPathName & "\"
    End If
 
    DoCmd.RunCommand acCmdAppMaximize
 
    ' Loop through all tables in the database.
    On Error GoTo RefreshLinks_Error
    Set dbs = CurrentDb()
    Set dbsLink = DAO.DBEngine(0).OpenDatabase(strPathName & strFileName)
    TableLength = Forms![Link Back End]!MeterBackground.Width / dbs.TableDefs.Count
    TableCount = 1
    Err = 0
    For Each tdf In dbs.TableDefs
        Forms![Link Back End]!ProgressMeter.Width = TableLength * TableCount
        TableCount = TableCount + 1
        ' If the table has a connect string, it's a linked table.
        If Len(tdf.Connect) > 0 Then
            SysCmd SYSCMD_SETSTATUS, "Linking " & tdf.Name
            Forms![Link Back End]!LinkingFile = tdf.Name
            Forms![Link Back End].Repaint
            tdf.Connect = ";DATABASE=" & strPathName & strFileName
            tdf.RefreshLink         ' Relink the table.
        End If
    Next tdf
DoCmd.Hourglass False
DoCmd.Close acForm, "Link Back End", acSaveNo
SysCmd acSysCmdClearStatus
Exit Sub
 
RefreshLinks_Error:
If Err = 3044 Then
    MsgBox "RefreshLinks: Unable to link to " & strPathName & strFileName & _
        vbCr & "       --Contact administrator.", vbCritical
ElseIf Err = 3024 Then
    MsgBox "RefreshLinks: Unable to find the file: " & strPathName & _
        strFileName & vbCr & "       --Contact administrator.", vbCritical
ElseIf Err = 3011 Then
    MsgBox "RefreshLinks: Unable to find the table: " & tdf.Name _
        & vbCr & "       --Contact administrator.", vbCritical
Else
    MsgBox "RefreshLinks: Unable to link data files." & Chr(13) & "Unable to run database." & _
        Chr(13) & Chr(13) & "'" & strFileName & Chr(13) & _
        "     needs to be in the same folder as" & Chr(13) & "'" & strFileName & _
        "(Folder: '" & strPathName & "')" & Chr(13) & "(Linked file: '" & tdf.Name _
        & "')" & Chr(13) & Chr(13) & "       --Otherwise contact administrator.)", _
        vbCritical
End If
CloseCurrentDatabase
 
End Sub
 
'Private Function GetFileName(FileName As String) As String
'GetFileName = (Mid(Dir(FileName), 1, Len(Dir(FileName))))
'End Function
 
Public Function StartUpLinkCheck()
    Dim dbs As Database
    Dim strName As String
   
    Set dbs = CurrentDb()
 
    ' Open linked table to see if connection information is correct.
    On Error Resume Next
    dbs.OpenRecordset (IIf(Len(DefaultTableName), DefaultTableName, "tblAnyTable"))
 
    ' If tblRecipientsis found, exit.
    If Err = 0 Then
        dbs.Close
        Set dbs = Nothing
        Exit Function
    End If
    Err = 0
 
    'Otherwise check if BE is in same path as FE
    If Len(DefaultPath) Then strName = DefaultPath Else strName = getpath(CurrentDb.Name)
    
    If StrComp(Right(strName, 1), "\", vbTextCompare) <> 0 Then
        ' Append trailing backslash to path.
        strName = strName & "\"
    End If
 
    'If BE is found, link to it and exit
    If Dir(strName & getfileroot(CurrentDb.Name) & "_be.mdb") <> "" Then
        RefreshLinks
        dbs.Close
        Set dbs = Nothing
        Exit Function
    End If
    
    DoCmd.OpenForm "Link Back End", , , , , acDialog
 
    'Optional ask user to locate BE instead of open "Link Back End" form.
'    strName = GetOpenMDBFile(, "Please locate the back end file")
'    If strName <> "" Then RefreshLinks getpath(strName), GetFileName(strName) Else CloseCurrentDatabase
End Function
'************** Code End *****************
 
Public Function getfileroot(FileName As String) As String
getfileroot = (Mid(Dir(FileName), 1, Len(Dir(FileName)) - 4))
End Function

Open in new window

0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 1500 total points
ID: 24400340
For ODBC, I use a table to store Server Names, Database names.

You then need a table to hold the names of all ODBC linked tables. This will hold both the local Access name (the name you want to give the table in Access) and the remote name (i.e. dbo.Table1).

Your code needs to retrieve the setting (server name, database name) you want from a form or a parameters table if you want to store the settings

You then needed to loop through each table listed in your tables table, drop the link and recreate it

Code below does that - this example uses trusted connections (and is SQL 2005).

For the SQL authentication  or syntax of other versions of SQL have a look at

http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer

Kelvin
Public Function RelinkSQLTables(lLink As Long)
On Error GoTo EH
 
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strServer As String
Dim strDatabase As String
Dim strConnect As String
Dim strTblServer As String
Dim strTblLocal As String
Dim rsTables As DAO.Recordset
Dim sSQL As String
 
Set db = CurrentDb()
 
''Build the dsn-less connection string
strServer = DLookup("[ServerName]", "USysDatabaseLink", "[LinkID] = " & lLink)
strDatabase = DLookup("[DatabaseName]", "USysDatabaseLink", "[LinkID] = " & lLink)
strConnect = "ODBC;Driver={SQL Native Client};Server=" & strServer & ";Database=" & strDatabase & ";Trusted_Connection=yes"
sSQL = "SELECT * FROM USysTables where Not IsNull(RemoteName);"
 
Set rsTables = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do Until rsTables.EOF
    For Each tdf In db.TableDefs
        If tdf.Name = rsTables!TableName Then
            db.TableDefs.Delete rsTables!TableName
            Exit For
        End If
    Next
        Set tdf = db.CreateTableDef(rsTables!TableName, dbAttachSavePWD, rsTables!RemoteName, strConnect)
        db.TableDefs.Append tdf
    rsTables.MoveNext
Loop
 
Exit_Sub:
    Exit Function
    
EH:
    Call ErrorManager(Err.Number, Err.Description)
    Resume Exit_Sub
 
End Function

Open in new window

0
 

Author Comment

by:CaroleTSullivan
ID: 24414061
thank you for all your help!  I was able to figure out a workaround using KevinSparks suggested code.  Thank you for the pointers!!
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

764 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