SQL Server ODBC Link to read/write access table

Posted on 2008-11-06
Last Modified: 2013-11-27
Hi All I have the following code working that it is relinking my ODBC table correctly from SQL Server 2005.  The only problem is it is linking all of them READ ONLY.  I need some of the tables to be Read/Write.  How can I do this?

The ODBC table structure is
Column Name Data Type Allow Nulls
ID      int      Unchecked
[DataBase]      nvarchar(50)      Checked
UID      nvarchar(50)      Checked
PWD      nvarchar(50)      Checked
UseTrustedConnection      bit      Checked
Server      nvarchar(50)      Checked
ODBCTableName      nvarchar(90)      Checked
LocalTableName      nvarchar(90)      Checked
DSN      nvarchar(50)      Checked
Option Compare Database

Option Explicit

' From



Public Function Connection(strDSN As String, _

                            strDataBase As String, _

                            strODBCTableName As String) As String

   Connection = "ODBC;"

   Connection = Connection & "DSN=" & strDSN & ";"

   Connection = Connection & "APP=2007 Microsoft Office system;"

   Connection = Connection & "DATABASE=" & strDataBase & ";"

   Connection = Connection & "Trusted_Connection=Yes;"

   Connection = Connection & "TABLE=" & strODBCTableName

End Function

Public Sub CreateLinked(strODBCTableName As String, _

                         strLocalTableName As String, _

                         strConn As String)

   Dim tbl As DAO.TableDef


   If (DoesTblExist(strLocalTableName) = False) Then

      'Set tbl = CurrentDb.CreateTableDef(strLocalTableName, _

                                         dbAttachSavePWD, _

                                         strODBCTableName, _


      'CurrentDb.TableDefs.Append tbl

      CurrentDb.TableDefs.Append CurrentDb.CreateTableDef(strLocalTableName, _

                                          acTable, _

                                          strODBCTableName, _




      CurrentDb.TableDefs(strLocalTableName).Connect = strConn


   End If

End Sub


'The DoesTblExist function validates the existence of a TableDef

'object in the current database. The result determines if an

'object should be appended or its Connect property refreshed.


Function DoesTblExist(strTblName As String) As Boolean

   On Error Resume Next

   Dim db As DAO.Database, tbl As DAO.TableDef

   Set db = CurrentDb

   Set tbl = db.TableDefs(strTblName)

   If Err.Number = 3265 Then   ' Item not found.

      DoesTblExist = False

      Exit Function

   End If

   DoesTblExist = True

End Function

Function LinkODBCandYRQTables() As Boolean

   On Error GoTo LinkODBCandYEARTables_Err

   Dim strConn As String


   Dim strDSN As String

   Dim strDataBase As String

   Dim strServer As String

   Dim strLocalTableName As String

   Dim strODBCTableName As String


   ' This is for the Research database

   ' that contains the actually ATD calculated and submitted data

   'Stop ' Adjust the DSN for the specific SQL Server Database connection

   strDSN = "Research"

   strDataBase = "Research"

   strServer = "sqladmin1"


   ' ---------------------------------------------

   ' Register ODBC database(s).

   ' ---------------------------------------------

   DBEngine.RegisterDatabase strDSN, _

                  "SQL Server", _

                  True, _

                  "Description=VSS - " & strDataBase & _

                  Chr(13) & "Server=" & strServer & _

                  Chr(13) & "Database=" & strDataBase


   ' ---------------------------------------------

   ' Link table.

   ' ---------------------------------------------

   'strLocalTableName = "_ODBCDataSources"

   'strODBCTableName = "ATD.ODBCDataSources"

   'strConn = Connection(strDSN, strDataBase, strODBCTableName)

   ' CreateLinked strODBCTableName, strLocalTableName, strConn


   ' The OBDC source table

   strConn = Connection("Research", "Research", "ATD.ODBCDataSources")

   CreateLinked "ATD.ODBCDataSources", "_ODBCDataSources", strConn


   ' The table that contains the available General YRQ that can be exported

   strConn = Connection("Research", "Research", "ATD.ExportGeneralYRQ")

   CreateLinked "ATD.ExportGeneralYRQ", "_GeneralYRQ", strConn


   ' The table that contains the available General YRQ that can be exported

   strConn = Connection("Research", "Research", "ATD.ExportTermYRQ")

   CreateLinked "ATD.ExportTermYRQ", "_TermYRQ", strConn


   LinkODBCandYRQTables = True



   Exit Function


   MsgBox Err.Description, vbCritical, "MyApp"

   Resume LinkODBCandYEARTables_End

End Function

Function CreateODBCLinkedTables() As Boolean

   On Error GoTo CreateODBCLinkedTables_Err

   Dim strTblName As String, strConn As String

   Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef

   Dim strDSN As String

   ' ---------------------------------------------

   ' Register ODBC database(s).

   ' ---------------------------------------------

   Set db = CurrentDb

   Set rs = db.OpenRecordset("Select * From _ODBCDataSources Order By DSN")

   With rs

      While Not .EOF

         If strDSN <> rs("DSN") Then

            DBEngine.RegisterDatabase rs("DSN"), _

                  "SQL Server", _

                  True, _

                  "Description=VSS - " & rs("DataBase") & _

                  Chr(13) & "Server=" & rs("Server") & _

                  Chr(13) & "Database=" & rs("DataBase")

         End If

         strDSN = rs("DSN")

         ' ---------------------------------------------

         ' Link table.

         ' ---------------------------------------------

         strTblName = rs("LocalTableName")

         strConn = Connection(rs("DSN"), rs("DataBase"), rs("ODBCTableName"))

         CreateLinked rs("ODBCTableName"), rs("LocalTableName"), strConn




  End With



  CreateODBCLinkedTables = True

  'MsgBox "Refreshed ODBC Data Sources", vbInformation



   Exit Function



   MsgBox Err.Description & vbCrLf & "With strConn = " & strConn, vbCritical, "MyApp"

   Resume CreateODBCLinkedTables_End

End Function

Open in new window

Question by:mjenck-yvcc

    Author Comment

    I forgot to mention that when I link through the menu system the tables are Read/Write.  I need to be able to make them Read/Write through VBA.


    Accepted Solution

    Found a solution

    Public Sub LinkTable(strServer As String, strDataBase As String, _
                         strODBCTableName As String, strLocalTableName As String)
      'MYSQLSERVER is the name of the server (or PC) where the MS SQL database resides
      'MYSQLDB is the name of the database (inside MYSQLSERVER) to connect
      'MYSQLTABLE is the name of the table (inside MYSQLDB) to connect
      'MYACCESSTABLE is the name that MYSQLTABLE will get as linked table inside Access
      'USER and PASSWORD are the MS SQL defined user and password
      Dim strConn As String
      strConn = DNSLessConnection(strServer, strDataBase)
      DoCmd.TransferDatabase acLink, "ODBC Database", strConn, _
                             acTable, strODBCTableName, strLocalTableName
    End Sub

    Public Function DNSLessConnection(strServer As String, strDataBase As String) As String
      '"ODBC;Driver={SQL Server};Server=SQLADMIN1;Database=MYSQLDB;Trusted_Connection=Yes"
       DNSLessConnection = "ODBC;Driver={SQL Server};"
       DNSLessConnection = DNSLessConnection & "Server=" & strServer & ";"
       DNSLessConnection = DNSLessConnection & "DATABASE=" & strDataBase & ";"
       DNSLessConnection = DNSLessConnection & "Trusted_Connection=Yes;"
    End Function

    This causes a dialog box to come up and the user must select the index fields.  Any way to eliminate the need for the dialog box?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now