[AccessD] Using Code to relink tables between Access Databases

You have to handle Access and ODBC connections slightly differently
For Access, you just pass the database name -  for ODBC, you pass the connection string
Here's the relevant snippets from my relinking code:

Const strBEName = "\myApp_BE.mdb"

Const strSQLConnect = "ODBC;DRIVER=SQL Server;" & _
"SERVER=myServer;" & _
"APP=Microsoft Data Access Components;" & _ 
"DATABASE=myDB;Trusted_Connection=Yes"

Function ConnectSQL()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Left$(tdf.Name, 3) = "tbl" Then
         renewlink tdf.Name, strSQLConnect, False
    End If
Next
End Function

Function ConnectBELocal()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Left$(tdf.Name, 3) = "tbl" Then
         renewlink tdf.Name, CurrentProject.Path  & strBEName, True
    End If
Next
End Function

Function renewlink(tablename As String, datafile As String, AccessDb As Boolean) As Long
On Error Resume Next
DoCmd.DeleteObject acTable, tablename
On Error GoTo 0

Select Case AccessDb
Case True
DoCmd.TransferDatabase acLink, "Microsoft Access", datafile, acTable, tablename, 
tablename, False
Case False
DoCmd.TransferDatabase acLink, "ODBC Database", datafile, acTable, tablename, 
tablename, False
End Select
End Function


-- 
Stuart

On 19 Aug 2009 at 14:14, Collins, Darryl wrote:

> Hi folks,
> 
> I usually use an Access FE (MDE) to a SQL Server BE and have a nice
> clean process to create and link selected tables each time the MDE opens
> up and chats with the back end.  Now I am trying to do the same thing
> Access to Access (2000 in case it matters).  And Do you think I can get
> the damn thing to work. Bah humbug.  I suspect it has something to do
> with the DAO connection string I am providing.  The connection is
> DNSless.
> 
> Using AccessFE and SQL Server BE the following code works great (the
> tables names are pulled down from a tblTables etc).
> 
> Set tbl = CurrentDb.CreateTableDef(rsSP("tblName"), dbAttachSavePWD,
> rsSP("sqlName"), DbDAOConStr)
> CurrentDb.TableDefs.Append tbl
> 
> Based on the DAO string being
> Public Const DbDAOConStr As String = "ODBC;Driver={SQL Server};" & _
>   "SERVER=SQL01DEV\SP4;DATABASE=EUC_COMMON;Network=DBMSSOCN;" & _
>   "Trusted_Connection=Yes"
> 
> '-------------------------------------------------
> 
> I changed the DAO conn string to:
> Public Const DbDAOConStr As String = _
> "ODBC;Driver={Microsoft Access Driver (*.mdb)};Dbq=" & pcstrDataSource &
> ";Uid=Admin;Pwd=" & pcstrPW & ";"
> 
> And used the code:
> DoCmd.TransferDatabase acLink, "ODBC Database", strCon, acTable,
> rsSP("sqlName"), rsSP("tblName"), False, True
> 
> Would work for Access to Access relinking, but I get the error when this
> happens.
> "Cannot use OBDC to ... link an external Microsoft JET or ISAM database
> table to your database"
> 
> 
> 
> If I change the connection string to:
> Public Const DbDAOConStr As String = _
> "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & pcstrDataSource &
> ";Uid=Admin;Pwd=" & pcstrPW & ";"
> 
> And rerun the code, I get the following Error: "Could not find
> installable ISAM"
> 
> I looked that up on MSKDB and followed their fix, but to no avail.
> Hmmmm.
> 
> Getting frustrated now. Anyone got any advice on the correct way to do
> this?
> 
>  - wish I could just use SQL server :-/
> 
> 
> Cheers
> Darryl (at a new email addy!) - new job.
> 
> Darryl Collins | IT PMO Integration Specialist
> Cash Management Platform (CMP) Program
> 
> ANZ, Level 23, 55 Collins Street, Melbourne
> Ph: +61 3 9652 1510 (Local: 03 9652 1510)
> Mobile: +61 418 381 548 (Local: 0418 381 548)
> Email: darryl.collins at anz.com <mailto:collinsd at anz.com> 
> 
> 
> 
> 
> "This e-mail and any attachments to it (the "Communication") is, unless otherwise stated, confidential,  may contain copyright material and is for the use only of the intended recipient. If you receive the Communication in error, please notify the sender immediately by return e-mail, delete the Communication and the return e-mail, and do not read, copy, retransmit or otherwise deal with it. Any views expressed in the Communication are those of the individual sender only, unless expressly stated to be those of Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any of its related entities including ANZ National Bank Limited (together "ANZ"). ANZ does not accept liability in connection with the integrity of or errors in the Communication, computer virus, data corruption, interference or delay arising from or in respect of the Communication."
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com