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

[Clifford Bass via AccessMonster.com]

Hi Jon,

Instead of using the TransferDatabase, you could try changing the
TableDef.Connect property and then doing a TableDef.RefreshLink. Here is an
example using Access databases that you can work from:

Public Sub RefreshLinkedTable()

Dim dbCurrent As DAO.Database

Set dbCurrent = CurrentDb
With dbCurrent.TableDefs("tblLinkToMe")
.Connect = Replace(.Connect, "Database1.mdb", "Database2.mdb")
.RefreshLink
End With
Set dbCurrent = Nothing

End Sub

Clifford Bass

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

[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

"Clifford Bass via AccessMonster.com" <u48370@uwe> wrote in message
news:a0caf4a4f99ea@uwe...

[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

"Tom" <rtm...@swbell.net> wrote in message
news:b0a251eb-64b1-4137...@m3g2000yqf.googlegroups.com...

[Clifford Bass via AccessMonster.com]

Hi Jon,

If Tom's suggestion solves your problem, then good! My suggestion was
based on your code which showed the deletion of the table and than immediate
recreation of it. So what you now describe is a little different. If you
are linking views instead of tables you could expand Tom's suggestion with
the inclusion of the storage of the indexes and their associated columns.
Then add them into the TableDefs through code.

All that is fine and good, but I really wonder if you are not doing
something that is really unnecessary. Maybe it would be better to use the
appropriate security and prevent access to the list of tables? And how would
you prevent someone from creating a new access database and simply doing
their own linking to the tables?

Just a couple of thoughts.

Clifford Bass

jon wrote:
>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 ?

--

[Dirk Horsten]

Private Sub connectMyself(dbVers As versProj)

Dim def As TableDef
Dim bmrcConnect As String, bmrcTbl, accessLink As String
Select Case dbVers
Case versRecette
bmrcConnect = "ODBC;DSN=TO DEV;SERVER=DEVSERVER"
Case versQualif
bmrcConnect = "ODBC;DSN=TO TEST;SERVER=QUALSERVER;"
Case versProd
bmrcConnect = "ODBC;DSN=TO PROD;SERVER=PRODSERVER"
Case versUnknown
bmrcConnect = ""
End
End Select
For Each def In CurrentProject.Application.CurrentDb.TableDefs
accessLink = def.Name
' Only the ODBC_* tables are linked
If Left(accessLink, 5) = "ODBC_" Then
def.Connect = bmrcConnect
def.RefreshLink
End If
Next
End Sub

> On Friday, December 18, 2009 11:12 AM jon wrote:

> Hi,
> I have 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 am using Access 2007

>>> On Monday, December 21, 2009 4:12 AM jon wrote:

>>> 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 cannot easily see all the tables that are used, and start

>>> to access them willy nilly.
>>>

>>> I am sure there must be a way to do this.

>>>
>>> Thanks for your reply Clifford.
>>>
>>> Any further suggestions anyone ?
>>>

>>> Jon

>>>> On Monday, December 21, 2009 2:24 PM Tom wrote:

>>>> 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 is probably overkill to do it everytime, but it
>>>> works for me.
>>>>
>>>> Using this method you do not 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

>>>>> On Monday, December 21, 2009 4:53 PM jon wrote:

>>>>> Sounds perfect Tom,
>>>>> I will give it a go in the morning.

>>>>>
>>>>> Thanks to Tom and everyone else that read my post.
>>>>>
>>>>> Jon

>>>>>> On Tuesday, December 22, 2009 1:34 PM Clifford Bass via AccessMonster.com wrote:

>>>>>> Hi Jon,
>>>>>>
>>>>>> If Tom's suggestion solves your problem, then good! My suggestion was
>>>>>> based on your code which showed the deletion of the table and than immediate
>>>>>> recreation of it. So what you now describe is a little different. If you
>>>>>> are linking views instead of tables you could expand Tom's suggestion with
>>>>>> the inclusion of the storage of the indexes and their associated columns.
>>>>>> Then add them into the TableDefs through code.
>>>>>>
>>>>>> All that is fine and good, but I really wonder if you are not doing
>>>>>> something that is really unnecessary. Maybe it would be better to use the
>>>>>> appropriate security and prevent access to the list of tables? And how would
>>>>>> you prevent someone from creating a new access database and simply doing
>>>>>> their own linking to the tables?
>>>>>>
>>>>>> Just a couple of thoughts.
>>>>>>
>>>>>> Clifford Bass
>>>>>>
>>>>>> jon wrote:
>>>>>>

>>>>>> --
>>>>>> Message posted via AccessMonster.com
>>>>>> http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>> JustCode Visual Studio Development Add-In by Telerik
>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/d174b33a-d86e-48e7-b381-fcd1938b6775/justcode-visual-studio-development-addin-by-telerik.aspx