Setting the primary key when using DoCmd.TransferDatabase acLink

[jon]

Hi,
I've created a module to create link tables to my Oracle database.

The module is very simple as follows:

Public Function CREATE_LINK_TABLE(ODBC_SCHEMA, ODBC_SOURCE_TABLE,
ODBC_LOCAL_TABLE, DSN, UID, PWD)
On Error Resume Next
DoCmd.RunSQL "DROP TABLE " & ODBC_LOCAL_TABLE
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=" & DSN & ";UID="
& UID & ";PWD=" & PWD & ";SERVER=myserver", acTable, ODBC_SCHEMA & "." &
ODBC_SOURCE_TABLE, ODBC_LOCAL_TABLE, False, True
End Function

The problem is that I get prompts for primary keys when using it to link to
some tables, is there a way I can also pass the primary key values in this
module, or is there another way of doing it ?

I want to be able to cycle through all the tables in my database and re-link
them, so that I can use either LIVE data or DEMO data.

Hope that makes sense.

Thanks in advance

Jon.

BTW, I'm using Access 2007

 

[jon]

Hi Clifford,
A very nice suggestion, but I'd like to know how to set the primary keys on
a new link table, as I suspect i'll be rolling the database out at different
locations needing different tables.

I also want to be able to link and delete tables automatically in VB, so if
a user looks they can't easily see all the tables that are used, and start
to access them willy nilly.

I'm sure there must be a way to do this.

Thanks for your reply Clifford.

Any further suggestions anyone ?

Jon

 

[Tom]

Up until a few days ago I had been using the TransferDatabase method
too, then ran into the same problem you are having. Doug Steele and
Stefan Hoffman in this group suggested using the TableDefs and I ended
up with the following:

Set rst = CurrentDb.OpenRecordset("Select * from tblTableName",
dbOpenSnapshot)
strODBC = "ODBC;Driver={SQL Server};Server=xxx;Database=xxx;WSID="
& Environ("UserName") & ";uid=xxx;pwd=xxx"

Do Until rst.EOF
strdboName = "dbo_" & rst!tablename
On Error Resume Next
CurrentDb.TableDefs.Delete (strdboName)
On Error GoTo ErrHandler
Set tdf = CurrentDb.CreateTableDef(strdboName, 0, rst!
tablename, strODBC)
CurrentDb.TableDefs.Append tdf

rst.MoveNext
Loop
CurrentDb.TableDefs.Refresh

Bascially, I store the name of all the tables I want to link to in
tblTableName, then each time the database is run, delete all the links
and reestablish... It's probably overkill to do it everytime, but it
works for me.

Using this method you don't get that annoying prompt for primary
keys. (note all my Tables have appropriate keys, but not the views I
use for reporting).

If you are connecting to multiple different DBs on the back end, you
could probably store the DB name in the table too and adjust the
strODBC on the fly.

Good luck

Tom

 

[jon]

Sounds perfect Tom,
I'll give it a go in the morning.

Thanks to Tom and everyone else that read my post.

Jon