Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 04-05-2012, 01:19 PM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,800
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Post Attach DSNLessTable for SQL Server Native Client 10.0

Download SQL Server Native Client 10.0 (have not tested Client 11.0 for Access 2012)
http://www.microsoft.com/download/en....aspx?id=16978

For Access 2010 SQL 2008 R2 Used sQL Server Migration Assistant for Access to migrate Access Linked Tables up to SQL Server (keep names same)

The connection string for SQL Server Security is included here. The linked tables are named dbo.TableNames - this code will rename them to leave out the "dbo.".
This approach saves all of the linked Access table names in a local table named SQL_Linked. The SQL_Linked table has a column of checkboxes described in comments below. This allows me to keep some tables linked to Access and to chose when and what tables get linked to SQL Server. The code is far from a polished product.

Code:
 
Function GetLinkedTablesForLocalTable() As Boolean
' Create a local table named SQL_Linked  ' A check in 3rd fild will delete Access linked and re-build SQL link
' 1st field text - "TableName", 2nd field yes/No "Linked" default =True, 3rd field yes/no "Relink" default=0
' Migrate All Access linked tables to SQL Server first. On mine a ODBC linkd shows dbo.TableName
' download and install SQL Server Native Client 10.0 on each client machine
          Dim dbs As DAO.Database
          Dim tdf As DAO.TableDef
          ' Loop through all tables in local DB
          GetLinkedTablesForLocalTable = False
10        Set dbs = CurrentDb
20        For Each tdf In dbs.TableDefs
30            If Len(tdf.Connect) > 0 Then  ' Table with connect string
40                If tdf.Connect <> ";DATABASE=" & MyDbName Then
50                    tdf.Connect = ";DATABASE=" & MyDbName
60                    Err = 0
70                    On Error Resume Next
80                    CurrentDb.Execute ("INSERT INTO SQL_Linked (TableName) VALUES ('" & tdf.Name & "' ) ;")
                      ' Table Named "SQL_Linked", field name "TableName" and value of tdf.name
                      ' Table SQL_Linked with a field named TableName must exist in local DB
90                    If Err <> 0 Then
100                       GetLinkedTablesForLocalTable = False
110                       Debug.Print "LOCAL " & tdf.Name ' these should not appear - reference only
120                       Exit Function
130                   End If
140               End If
150           End If
160       Next tdf
170       GetLinkedTablesForLocalTable = True        ' Refresh the table SQL_Linked and view all the linked table names
 
End Function
 
' Use this for the stConnect shown above
Function ModifiedRefreshDNSLess2(TableName As String) As String ' temp test database
      Dim ConnectString As String
      Dim strConnectionString As String
      Dim scn As String
      Dim sLocalName As String
      Dim UID As String
      Dim PWD As String
10    UID = "datamig"     ' temp SQL name change later
20    PWD = "datamig"
30    sLocalName = TableName  ' "Const_Seed" ' <<<---manually add a table name
      Dim DataBaseName As String
40    DataBaseName = "RegulatoryDB"
50    strConnectionString = "ODBC;DRIVER=SQL Server Native Client 10.0;" & _
          "SERVER=DenReg-Test;DATABASE=" & DataBaseName & ";" & _
          "UID=" & UID & ";" & _
          "PWD=" & PWD & ";" & _
          "Table=DBO." & sLocalName & ";Option=3;"
60        ModifiedRefreshDNSLess2 = strConnectionString
          'Debug.Print strConnectionString
End Function
Public Sub SQL_Linked_Process() ' Access 2010 SQL 2008 R2
      ' A local Table SQL_Linked with fields TableName, linked, relink (the last two are yes/no check boxes)
      ' A procedure populates SQL_Linked with all the Access Linked table names.
      ' Placing a check (yes) in Relink will delete the Access Linked Table, and append a new Linked Table with DSNLess connection
      ' It is necessary to have migrated (updated) the Access DB to SQL - assume they have the same name.
      ' For data migration purpose and 1 level testing, a user datamig was created with sqlserver security
      Dim rsSQLLinked As Recordset
      Dim RecordsCount As Integer
      Dim Counter As Integer
      Dim td As TableDef  ' for table SQL_Linked
      Dim tdLinked As TableDef ' for new linked table
10    On Error Resume Next
20    Set rsSQLLinked = CurrentDb.OpenRecordset("SQL_Linked", dbOpenDynaset)
30    rsSQLLinked.MoveLast
40    RecordsCount = rsSQLLinked.RecordCount
50    rsSQLLinked.MoveFirst
60    Debug.Print "Number of Linked Tables " & RecordsCount
      '  /////  Delete the linked tables that have a check in the Relink  Column of the local table \\\\\
70    If RecordsCount <> 0 Then
80        For Counter = 1 To RecordsCount
              'Debug.Print Counter & "/" & RecordsCount & " Field value " & rsSQLLinked.Fields(0).Value & "  " & rsSQLLinked.Fields(2).Value
90                    If rsSQLLinked.Fields(2).Value Then                 ' if Relink checkbox is true then
100                         For Each td In CurrentDb.TableDefs
110                               If td.Name = rsSQLLinked.Fields(0).Value Then
120                                     CurrentDb.TableDefs.Delete rsSQLLinked.Fields(0).Value
130                                     Err.Clear
140                               End If
150                         Next
160                   End If
170       rsSQLLinked.MoveNext
180       Next Counter
190   CurrentDb.TableDefs.Refresh
200   Else
210       MsgBox "There are no records in the table", vbOKOnly, "SQL_Linked_Process"
220       Exit Sub
230   End If
      ' ////////////// create and  Relink to SQL Server if check is in Relink column  ///////////
240   If RecordsCount <> 0 Then
250       rsSQLLinked.MoveFirst
260       For Counter = 1 To RecordsCount
              'Debug.Print Counter & "/" & RecordsCount & " Field value " & rsSQLLinked.Fields(0).Value & "  " & rsSQLLinked.Fields(2).Value
270                   If rsSQLLinked.Fields(2).Value Then                 ' if Relink checkbox is true then
                      ' Add new linked table here
280                           Set tdLinked = CurrentDb.CreateTableDef(rsSQLLinked.Fields(0).Value)
290                           tdLinked.Connect = ModifiedRefreshDNSLess2(rsSQLLinked.Fields(0).Value)
300                                   tdLinked.SourceTableName = "dbo." & rsSQLLinked.Fields(0).Value
310                                   CurrentDb.TableDefs.Append tdLinked
320                                   CurrentDb.TableDefs(rsSQLLinked.Fields(0).Value).RefreshLink
330                                   CurrentDb.Containers("Tables").Documents.Refresh ' doesn't refresh table icon
340                   End If
350       rsSQLLinked.MoveNext
360       Err.Clear
370       Next Counter
380   CurrentDb.TableDefs.Refresh
390   RerefreshLinkedTables
400   Else
410       MsgBox "There are no records in the table", vbOKOnly, "SQL_Linked_Process"
420       Exit Sub
430   End If
      ' ////////////////
440   Set rsSQLLinked = Nothing
450   Set tdLinked = Nothing
460   Set td = Nothing
470   Exit Sub
End Sub
Sub RerefreshLinkedTables() ' refreshes Linked tables doesn't refresh icon
          Dim dbs As DAO.Database
          Dim tdf As TableDef
10        Set dbs = CurrentDb
20        For Each tdf In dbs.TableDefs
30            If Len(tdf.Connect) > 0 Then
40                    tdf.RefreshLink
                      'Debug.Print tdf.Name
50            End If
60        Next tdf
70        Set dbs = Nothing
End Sub
Public Function DropAllLinkedTables()
10        For Each tdf In CurrentDb.TableDefs    ' If the table has a connect string, it's a linked table.
              ' Drop all tables and rebuild again from local table SQL_Linked where names are stored
20            If Len(tdf.Connect) > 0 Then
30                DoCmd.DeleteObject acTable, tdf.Name
40            End If
50        Next tdf
End Function

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Reply

Tags
connection string , dsnless , native client 10 , vba access

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Use Default Email Client and Attach file craigachan Modules & VBA 18 10-12-2010 09:56 PM
SQL Server 2008 MDAC ODBC Native Client 10 Rx_ SQL Server 5 04-08-2010 11:32 AM
Changing connection to SQL Native client arichins General 9 11-24-2009 10:54 PM
Client/Server? smp245 General 5 06-18-2007 09:24 AM
DB for client-server use jayke General 7 10-10-2004 11:03 PM




All times are GMT -8. The time now is 07:48 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World