Solved

how to use VBA to connect to ODBC SQL Server

Posted on 2009-05-14
6
791 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
  • 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now