Linking 1 table in code 1

[nickperez22]

I just created a new table in code, how do I link it in code to the front end. Table name "Titles

 

[JoanneM]

Hi Nick
Have the path of the connecting database ready and copy it into function in quotes
eg
Function ConnectToAccess("c:\mydocuments\db1")

copy the code below into a module eg Bas_Attach

call the function either from the immediate window or a command button

It returns an integer to validate successful completion

Function ConnectToAccess(DatabasePath As String) As Integer
On Error GoTo connectToAccesserr
    Dim tbl As TableDef
    Dim stablename As String
    
 
    For Each tbl In CurrentDb.TableDefs
        If tbl.Connect <> "" Then       ' Isn't a local table
            stablename = tbl.Name
            CurrentDb.TableDefs.Delete (stablename)
            DoCmd.TransferDatabase acLink, "Microsoft Access", _
                            DatabasePath, acTable, stablename, stablename
        End If
    Next
    
    CurrentDb.TableDefs.Refresh
   DoCmd.OpenForm "switchboard"

 
 
ConnectToAccess = -1
connecttoaccesserrexit:
Err = 0
Exit Function

connectToAccesserr:
MsgBox Error$(Err)
ConnectToAccess = 0
GoTo connecttoaccesserrexit


End Function

Hope this is what you want

regards

Jo

 

[nickperez22]

Thanks, but I'm having a slight problem and that is that I have 2 backend databases linked to the front end, so as soon as the function finds one link that is not in the database the funtion is pointing to I get an error.

In my case I do not need to refresh links or go through all the tables to re-establish links. I simply need the code to link this one table the front end after I have created it.

 

[JoanneM]

H Nick try this
Just copy the whole into your module
and try it by calling the sub from the immediate window

Sub linkOneTable()
dim stablename as string
        stablename = "Your Table name here"
        CurrentDb.TableDefs.Delete (stablename)
        DoCmd.TransferDatabase acLink, "Microsoft Access",  DatabasePath, acTable, stablename, stablename
CurrentDb.TableDefs.Refresh
end sub

regards

Jo