[AccessD] Using Code to relink tables between Access Databases

Stuart,

Thank you very much for this.  I will keep this code for future use and
test it out now to see if it is going to suit my needs. I think I am
close now to getting this to work, although your code maybe a much
better method.  Testing testing testing..

Cheers
Darryl. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Wednesday, 19 August 2009 2:44 PM
To: Access Developers discussion and problem solving
Subject: Re: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.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."