[AccessD] Using Code to relink tables between Access Databases

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."