Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2581
  • Last Modified:

SQL Server ODBC Link to read/write access table

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
' http://support.microsoft.com/default.aspx?scid=kb;en-us;208295
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

  • 2
1 Solution
mjenck-yvccAuthor Commented:
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.

mjenck-yvccAuthor Commented:
Found a solution

Public Sub LinkTable(strServer As String, strDataBase As String, _
                     strODBCTableName As String, strLocalTableName As String)
  ' http://www.access-programmers.co.uk/forums/showthread.php?t=156294
  '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?

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now