VBA to Link all tables from remote DB 1

[AppStaff]

Anyone have sample code on how to link all tables from a remote database to your current database?

My main issue is in referencing the remote database. Any Help is appreciated.

 

[downwitchyobadself]

By "remote" do you mean not visible on the app's network? Over the internet?

I have code to do this to any file visible through the Windows environment, but it's long, and I'm not sure it's what you want.

 

[AppStaff]

Sorry for the confusion. By remote I simply mean tables not in the current database. On a split database (forms separate from data) I want to click a button (or other trigger) from the forms database to attach all the tables from a data file which the name and location for it will be provided in a variable.

 

[downwitchyobadself]

Okay. Pretty straightfoward stuff, I think there's a link or two on the MS site for it as well, but anyway. My code deletes all linked tables in the current front end, and then reattaches to all existing tables in a given back end (as passed in variable pstrFullFile, includes path of course). Copy and paste into public module:

Public Function Reattach(ByVal pstrFullFile As String) As Boolean
On Error GoTo HandleErrors

    Dim tdfSource   As DAO.TableDef
    Dim tdfMe       As DAO.TableDef
    Dim rst         As DAO.Recordset
    Dim dbMe        As DAO.Database
    Dim dbSource    As DAO.Database
    Dim i           As Integer
    Dim bleLinkLocal    As Boolean

    Dim intCount    As Integer
    Dim varSysCmd   As Variant
    
    DoCmd.Hourglass True
    Reattach = True
    
    Set dbSource = OpenDatabase(pstrFullFile)
    Set dbMe = CurrentDb
    
    'this part deletes attachments
    With dbMe
        For i = .TableDefs.Count - 1 To 0 Step -1
            Set tdfMe = .TableDefs(i)
            With tdfMe
                If (.Attributes And dbAttachedTable) = dbAttachedTable Then
                    dbMe.TableDefs.Delete (.Name)
                End If
            End With
        Next i
    End With
    
    dbMe.TableDefs.Refresh
    i = 0
    
    'this part attaches to pstrFullFile tables
    With dbSource
        varSysCmd = SysCmd(acSysCmdInitMeter, "Linking tables from " & pstrFullFile & "...", .TableDefs.Count)
        For Each tdfSource In .TableDefs
            bleLinkLocal = False
            If Left(tdfSource.Name, 4) <> &quot;MSys&quot; Then
                For Each tdfMe In dbMe.TableDefs
                    If tdfMe.SourceTableName = tdfSource.Name Then
                        bleLinkLocal = True
                        Exit For
                    End If
                Next tdfMe
                Set tdfMe = Nothing
                If Not bleLinkLocal Then
                    Set tdfMe = dbMe.CreateTableDef(tdfSource.Name)
                    With tdfMe
                        .Connect = &quot;;Database=&quot; & dbSource.Name
                        .SourceTableName = tdfSource.Name
                    End With
                    dbMe.TableDefs.Append tdfMe
                    'Hide table:  tdf.Attributes = dbHiddenObject
                    tdfMe.RefreshLink
                End If
                i = i + 1
                varSysCmd = SysCmd(acSysCmdUpdateMeter, i)
            End If
        Next tdfSource

        'this part I use to record attached file name in a table utblSysAdmin.  Delete it if you don't want it.        
        Set rst = .TableDefs(&quot;utblSysAdmin&quot;).OpenRecordset(dbOpenTable)
        
        With rst
            .Edit
                !AttachFile = pstrFullFile
            .Update
            .Close
        End With
        
        .Close
    End With

ExitHere:
    'If Not (dbSource Is Nothing) Then dbSource.Close
    Set dbMe = Nothing
    Set tdfMe = Nothing
    Set tdfSource = Nothing
    varSysCmd = SysCmd(acSysCmdRemoveMeter)
    Application.Echo True
    DoCmd.Hourglass False
    Exit Function
    
HandleErrors:
    Reattach = False
    If Err = 3011 Then
        If MsgBox(&quot;Table &quot; & tdfMe.Name & &quot; not found.  Would you like to &quot; _
         & &quot;permanently remove the link to this table?&quot;, vbYesNo + vbQuestion) = vbYes Then
            dbMe.TableDefs.Delete (tdfMe.Name)
        End If
        Resume Next
    Else
        'your generic error handler here
        Reattach = False
        Resume ExitHere
    End If
    
End Function

Call it by saying If Reattach("MyBackEndFileNmWithPath.mdb&quot = True then carry on, or some such; use boolean function outcome to halt other processes if you like.

Hope this helps.

 

[AppStaff]

Thanks so much for your help! I'll give this a try.

 

[abysas]

Another very good source of information of how to link tables programatically is Access Security FAQ:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q207793

 

[danvlas]

You could try this as well:

Sub RefreshLinks()
Dim tdf As TableDef
Dim newconn As String
newconn = "\\ShareName\FolderName\FileName.mdb"
If Len(newconn) = 0 Then
MsgBox "You haven't selected any database. Table links will not be changed", vbInformation, "No database selection"
Exit Sub
End If

On Error GoTo ErrLinks
refreshconnection:
newconn = ";Database=" & newconn
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = newconn
tdf.RefreshLink
End If
Next
MsgBox "Tables successfully linked"

Exit Sub

ErrLinks:
Select Case Err.Description
Case 3011
MsgBox "The database " & Right(newconn, Len(newconn) - 10) & " does not contain the " _
& "table '" & tdf.NAME & "'. Please select another database.", vbCritical, "Invalid database"
Case Else
MsgBox "Error: " & Err.Number & "-" & Err.Description
End Select
Exit Sub
End Sub

You can use the OpenDialog function from:

http://www.mvps.org/access/api/api0001.htm

to browse for the newconn string (the bold line).


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[AppStaff]

downwitchyobadself

I'm having trouble converting your code to my needs. I would like the parameter in your function to be read from a table.

reattach(pstrfullfilename)

dim pstrfullfilename as string
dim strDSN as string
dim strDataFile as string

strDSN=dlookup("DSN","tblReattach&quot
strDataFile=dlookup("DataFile","tblReattach&quot
pstrfullfilename= strDSN & strDataFile

when I add these lines to your code I get wrong number of arguments. Can you help me convert this so that the variable uses my dlookups?

 

[AppStaff]

Danvlas,

Please Correct me if I'm wrong but this code only refreshes existing links and doesn't add links when none exist?

I need code that will do both and I need to feed it the path of the data file through a variable.

Thanks alot for you time and any further suggestions/clarification.

 

[danvlas]

"Please Correct me if I'm wrong but this code only refreshes existing links and doesn't add links when none exist?"

....hmmmmm....
You have a front end app. It has a TableDefs collection. The TableDefs collection includes the linked tables as well as the native ones. Each linked table has a Connect property that says which type of table it is and where it is located. There is NO WAY that a linked table has no link. But the link may be broken...so you have to refresh it.

"I need to feed it the path of the data file through a variable"

You already have a variable: newconn
Get the Open/Save dialog from the link I posted and use:

newconn =GetOpenFile()

The code works fine (I've been using it for more than a year) if alllll the linked tables come from the same mdb file. If you have more linked databases, it needs to be changed.

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[AppStaff]

danvlas,

What I'm trying to say is that I have two needs;

1) refresh links (for when data file has been moved)
2) attach links (for new installation where fe has no links yet)

does your code do both? It didnt seem to.

 

[danvlas]

It does both.
Try this:
when opening the main switchboard, open a recordset based on one linked table (usually a lookup one with few records). If the link is broken it will generate an error. Trap the error and trigger RefreshLinks and you'll see what happens...

Something like:

Sub TestLinks()
Dim rst As DAO.Recordset
On Error GoTo ErrHandler
Set rst = CurrentDb.OpenRecordset("LinkedTableName&quot
rst.Close

Finish:
Set rst = Nothing
Exit Sub
ErrHandler:
RefreshLinks
Resume Finish
End Sub

Good luck
Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[Quintios]

Would either of these solutions work in my situation? I have a front-end that will be placed somewhere, rather randomly, on the network. The back end is visible and available on the network in a centralized location. What I'd like to do is use an INI file, some sort of text file, that will contain the path to the back end. When the user opens up the front end on his workstation, the front end will look at the INI file and grab the path to the back end and then link up all the tables.

How would I accomplish this?
Onwards,

Q-

 

[KornGeek]

Quintios,
If I understand correctly, you shouldn't have to use code at all. With a fixed path to the back end, you can just link the tables in the master version of the front-end. When you distribute this, it should already point to the correct path to the back end.

Is there something I'm missing here?

 

[Quintios]

Like I said, it will be going in a random place, and the INI file will need to be edited by the enduser to point to the back end. The enduser will not be allowed (or able for that matter!) to edit the front end structure nor the back end structure.

This datbase is distributed per a "copy this from the server and install it on your computer" type of arrangement. Endusers copy the front end, and a particular project will copy the back end and put it in a central location. There is no way of knowing ahead of time where the back end will be located. We have a very very large ( >40,000 workstations) network that is available worldwide. This database is not unique and will be used one implementation per project, then archived as you would a project document. For the next project, a "fresh" copy of the database is copied and implemented in a similar fashion.

I've posted before on this topic but not received any workable solutions. I know this is possible as there's another db around here that does it, but I don't have access to the source VBA.

The front end needs to look at a text file that informs the front end where the back end will be. Again, there's no way of knowing ahead of time what the path will be.

Thanks in advance.
Onwards,

Q-

 

[danvlas]

You could try

Sub readpath()
Dim myBackEndPath As String
Open "C:\MyPath.ini" For Input As #1
Line Input #1, myBackEndPath
MsgBox myBackEndPath
Close #1
End Sub

MyPath.ini is a text file that contains the path and name of the back end in the first line.

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[AppStaff]

My thanks goes out to everyone that had such helpful and thoughtfull replies on this post.

 

[downwitchyobadself]

Sounds like you've worked it out?

I have no idea why those parameters you were passing in my code wouldn't work--must've been a problem with the values in your table? Did you have a file extension on the name, and \ between path and name?

I use a variation of the GetOpenFile() procedure to return the path & filename as the argument passed to Reattach(). I have a lot more code on the same subject if you need it; was trying to keep things simple