VBA Code to Add Linked Table with Primary Key

[Ed S.]

(MS Access at version 97.)

Does anyone have a definite answer.

Been trying to add a Linked Table with a primary key using VBA. (Can be accomplished mannually using New Tables Wizard (very easy)).

Here is the main issue: Since a Linked Table w/Primary key can be done manually, would seem reasonable it can be done programatically (VBA)?

If the Linked Table must be deleted first - that's ok because the database is on a server and connected via odbc. I am only interested in adding (not modifying) the Linked table w/primary key. Once in place MS Access does not allow us to modify a Linked table's primary Key(s).

********
The following code gets real close but when executed, recieve error message: Run time error'3367':
Can't Append. An object with the name already exists in the collection.

Option Compare Database
Option Explicit
Dim db As Database
Dim tdf As TableDef
Dim strDSNName As String
Dim strTableName As String
Dim Idx As Index
Dim Fld As Field
'
Sub BuildTableLink()

Set db = CurrentDb
Set strTableName = "NewTable"
'
Set tdf = db.CreateTableDef(strTableName)
tdf.Connect = "ODBC;DSN="DSN01";;TABLE=" & strTableName & ""
'
tdf.SourceTableName = "ExternalTableName"
'
db.TableDefs.Append tdf
'
Set Idx = tdf.CreateIndex("PrimaryKey")
Set Fld = tdf.Fields("FldA")
Idx.Primary = True
Idx.Unique = True

* * * * Following line Failes * * * *
Idx.Fields.Append Fld
tdf.Indexes.Append Idx
'
End Sub

Watch variable indicates 0 objects for Fields in Idx collection???

To reemphasize: tring to add a Linked Table with primary key using VBA.

 

[dmckinney]

which line gives the error.

I suspect it's the name of the PK index which is duplicated. i.e. it's on appending the index that you get the error, and not on linking the table.

However it surprises me that you should be allowed to add a primary key to a linked table (manually or otherwise) but I'll take your word for it.

Dave.

(I presume you're saying there's no key on the table on the server, and you want to add a key client side. If there's already a key on the server then there's nothing needs doing. Just link the table and the fact that there's a primary key will be picked up by Access and enforced by the server.)
This message was edited by dmckinney on 2003-01-04 07:24

 

[Ed S.]

Thank you for the reply Dave.

in the original posting look for:
* * * * Following line Failes * * * *
Idx.Fields.Append Fld

Yes, can link the table - is at the Append that the code crashes.

MS Access does allow the Primary Key property to be assigned at Table, Design, Add New, Linked Table time.

Back to main issue: Since a Linked Table w/Primary key can be done manually, would seem reasonable it can be done programatically (VBA)?

 

[dmckinney]

Hi again.

Can you pls clarify...are you adding a primary key that doesn't exist on the table you are linking? Or are you simply linking a table which has a primary key.<quote>
"MS Access does allow the Primary Key property to be assigned at Table, Design, Add New, Linked Table time. "</quote>

Can you be more specific? What is the exact procedure and what is it intended to achieve. I'm having trouble following your explanations.

Thanks,

Dave.
This message was edited by dmckinney on 2003-01-05 06:50

 

[Ed S.]

Hi,

I appreciate your continued interest and thank you once more.

** Simply linking to table on a server (AS/400) that has primary key assigned.

To your point of letting the server enforce Primay Keys: I concur. - will test this method on Monday, Jan 5th, 2003. Not sure as to how AS/400 will handle a Client imposed problem (say duplicate keys for a unique key file).

There is a great amount of detail that would require explanation in order to understand the basis for my question.

Here is a brief:
There are about 25 tables linked to the AS/400 in one MS Access DB. And, there are multiple AS/400's with multiple library/table sets. Same table name, different library name. (Same scenario as same table name, different path/directory.)

So, the problem I am trying to solve is how to use a single MS Access DB to manage several servers with varying table locations. (MS Access will be based on different Citrix Servers.)

Objective is Dynamic Re-link Capable software.

I have solved all issues and have written a VBA procedure that reads through table collection, relinks to server/table based on DSN properties. For example, I can run the procedure that points to Development environment, test software, and then run procedure to point back to a Production Environment. One MS Acces DB supports multi AS/400's and multi environments.

To conclude. The ReLink subroutine works to expectation with the single exception of Linked Tables w/primary keys (main issue). If I can resolve this coding issue, then I have a functional solution. The alternative is some re-engineering, i.e. let server enforce data integrity and send message to client (ouch).

So, if VBA code can emulate New Table - Wizard process described earlier in main issue, then objective is accomplished.

 

[dmckinney]

In my own tests (Acc 2000) I'm unable to put a primary key on a linked table (via Access interface). I can link to a table which has a primary key. (via interface or VBA). So this is where I fail to follow your logic

Scenario 1) Primary key exists on server.
Just link the table, the server will enforce your primary key.

Scenario 2) Primary key doesn't exist on server. You won't be able to add it to the client because the client can't possibly enforce what goes on at the server.

I reiterate that I've been unable to change any of the properties of a linked table from the client. Am I missing something?

(Should say that my tests involve mdb frontend and mdb backend.)

 

[Ed S.]

Dave, continued thanks.

I could not wait 'til monday - just finished testing table link without a primary key (or unique identifier) on table's Properties. (your Scenario 1)

First however, here are the steps to link to an external table using ODBC.

0. Open MDB file.
1. Select--> Tables
2. Select--> New
3. Select--> Link Table

note: the next steps may not be presented to you, but here goes.

4. in box "Files of type:" select--> ODBC Databases.
5. click on --> Machine Data Sources tab.
6. select Data Source Name to be used.
6.1 at this point, all tables in DSN list appear. therefore, Select a table for link.

now the form is presented stating "Select Unique Identifier"
7. Select field(s) up to ten. Here is where the unique key property is set by MS Access.

* * * end of procedure to link * * *

I then tested linking to a table with out assinging a Unique Identifier and the table does have a primary key on server side.

Results are: not allowed to add/insert record. Without a unique identifier, (which is displayed as a Primary Key property in design mode of table), I can only view and update - not insert record. Must have a unique identifier in order to Add a record in MS Access (97)!!!

Is unique identifier same as Primary Key?

seems odd VBA code can not support the process the table wizard performs... would be nice if MS Access was like Excel an generated macro code - could see what is occuring.

 

[dmckinney]

ok...I understand finally what you're saying.

Not good news I'm afraid.

What the wizard is saying is that it failed to detect a primary key on the server. Had it done so, it wouldn't have asked you to identify one.

I set up a test here with two tables one with a primary key, and the second without, and tried to link the tables.
For the table with a primary key...no problem. It's detected and enforced.

For the table without the PK...

...I tried your method with DAO and then with ADOX. I got the same result i.e. that it wouldn't allow me at any stage to append an index to the table. I end up with a table with no PK and which is thus not updatable.

I searched MSDN for additional info and found nothing.

So the only possible solution I can propose is to try to put a primary key on the server table. (Not knowing AS400 I don't know if this is feasible.)

Sorry I can't be more help...I'll let you know if I have any brainwaves.

dave.
This message was edited by dmckinney on 2003-01-06 05:38

 

[Ed S.]

Dave,

I too have done additional testing and have a good understanding of where you are at.

The problem is in the DB table design on AS400. There is no primary key asigned on the physical table (file). I added one and bingo - the table was VBA created with Primay Key automagically assigned.

I have been using what is known as a "Logical file" (closer to a view in SQL or alternate index). This is a very common technique - physical files are in "Arrival Sequence" and the logical files [can] enforce DB rules.

So, I understand what you are saying - put a key on physical file/table and we get results on link process that allows add/insert to table/file.

Not sure where to go from here. I would have to enhance all physical tables (41) in existing system in order to facilitate a dynamic table linking process.

Or, back to main issue: is there a way to assign unique identifier to a table, at table link time, in VBA code??? MS Access does support this process mannually.... would reason the process could be VB replicated.

Thanks for all the help and understanding. If anything comes to mind, please advmise.

 

[Ed S.]

Dave,

here is some follow-up info from MSDN. If I understand correctly, ADO objects' properties can be set via the CreateProperty method.

"Most of the properties you can set for DAO objects are DAO properties. These properties are defined by the Microsoft Jet database engine and are set the same way in any application that includes the Jet database engine. Some properties that you can set for DAOobjects are defined by Microsoft Access, and aren't automatically recognized by the Jet database engine. How you set properties for DAO objects depends on whether a property is defined by the Jet database engine or by Microsoft Access."






There appears to be a solution. However, I am not an expert VB programmer, so not exactly sure how to effect the method.