Is there a way by using code (VBA) to refresh linked tables where the drive

letter could be any letter?.



The reason is that the database is sitting on my local machine C: but I

intend to sit it on a server where the drive letter can be seen as O: or

even S: and may have other users using it.



Regards



Graham

--0-843989826-1005933728=:75788

Content-Type: text/plain; charset=us-ascii





 Use a universal naming convention (UNC) path such as:

servername\sharename\filename

example: \\server1\public\filename.ext

example: \\200.128.25.25\c\public\filename

Then it doesn't matter if or how the users are mapped to the networked resource.

rock on!

 

  G Worley <worleygs@h...> wrote: Is there a way by using code (VBA) to refresh linked tables where the drive

letter could be any letter?.



The reason is that the database is sitting on my local machine C: but I

intend to sit it on a server where the drive letter can be seen as O: or

even S: and may have other users using it.



Regards



Graham












---------------------------------

Do You Yahoo!?

Find the one for you at Yahoo! Personals.

It's definitely possible with DAO--probably also w/ADO (ADOX?), that I'm not

sure about.  The thing to change is the .Connect properties of the relevant

TableDef objects.  Don't forget to call .RefreshLink after you make that

change.



Here's some DAO code I'm using to programmatically change table link

locations in one of my apps:



' ===========================================

Public Function ChangeLinks(intLocation As BackEndLocation) As String

Dim tdfLoop As DAO.TableDef

Dim db As DAO.Database

Dim strDirectory As String

Dim strConnect As String

Dim intCounter As Integer

On Error GoTo Finish



   

   Select Case intLocation

      Case production

         strDirectory = g_strcProduction

      Case sandbox

         strDirectory = g_strcSandbox

   End Select

   

   strConnect = g_strcPrefix & strDirectory & g_strcDBName

   

   DoCmd.Hourglass True

   

   Set db = CurrentDb

   Call SysCmd(acSysCmdInitMeter, "Relinking tables", db.TableDefs.Count +

1)

   intCounter = 0

   For Each tdfLoop In db.TableDefs

      intCounter = intCounter + 1

      If Len(tdfLoop.Connect) > 0 Then

         ' Call SysCmd(acSysCmdUpdateMeter, "Relinking: " & tdfLoop.Name,

intCounter)

         Call SysCmd(acSysCmdUpdateMeter, intCounter)

         If tdfLoop.Connect = strConnect Then

            Debug.Print tdfLoop.Name & " already pointing at " & strConnect

         Else

            Debug.Print "Setting link for table " & tdfLoop.Name

            tdfLoop.Connect = strConnect

            tdfLoop.RefreshLink

            ' DoEvents

         End If

      End If

   Next tdfLoop

   Set db = Nothing

   'Call SysCmd(acSysCmdRemoveMeter)

   Call SysCmd(acSysCmdSetStatus, "Linked tables now point to " &

strConnect)



Finish:

   DoCmd.Hourglass False

   Select Case Err.Number

      Case 0

         ' do nothing

         ChangeLinks = "Linked tables now point to " & strConnect

      Case Else

         ChangeLinks = "Problem!"

         Call ErrorNoticeCoordinator.MailError(g_strcAppName, m_strcModName,

m_strFuncName)

         #If DEBUG_ME Then

             Debug.Assert False

             Resume

         #End If

   End Select



End Function

' ===========================================



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: G Worley [mailto:worleygs@h...]

Sent: Friday, November 16, 2001 9:30 AM

To: Access

Subject: [access] Linked Tables





Is there a way by using code (VBA) to refresh linked tables where the drive

letter could be any letter?.



The reason is that the database is sitting on my local machine C: but I

intend to sit it on a server where the drive letter can be seen as O: or

even S: and may have other users using it.



Regards



Graham