Link and Index Table

[SQUASHJUNKIE]

Hi,

I'm having a couple of problems attaching a table via an ODBC link....I'm using the following code
______________
Function AttachTable() As Variant
On Error GoTo AttachTable_Err

Dim db As Database
Dim tdef As TableDef
Dim strConnect As String
Dim tIdx As Index 'index for table


Set db = CurrentDb()
strConnect = "ODBC;SYSTEMDSN=C:\Program Files\Common Files\ODBC\Data Sources\WSPAIN"
Set tdef = db.CreateTableDef("ARTRAN")

Set tIdx = tdef.CreateIndex("SYSDOCID")
tIdx.PrimaryKey = True
tIdx.Unique = True

tdef.SourceTableName = "ARTRAN"
db.TableDefs.Append tdef

AttachTable_Exit:
Exit Function

AttachTable_Err:
MsgBox "Error: " & Str(Err) & " - " & Error$ & " occured in global module."
Resume AttachTable_Exit

End Function
______________

I'm getting stuck in the following areas :-(

1. Even though the ODBC is specified, the dialog box for the ODBC still opens and prompts me for the ODBC connection that I want to use, in this case "WSPAIN". When I check the location spec'd in my code, the folder is empty....I thought that this was the loc'n for ODBC connections, but it seems it isnt! (Using XP pro)
2. The table I am linking has multiple indexes, and I need to spec these in my code. I think I have the code right to set the primary key (although not sure), but how do i go about adding other unique record identifiers?

I am new to writing code in access, having done it all before with macros, so its a real steep learning curve for me....you'll therefore have to excuse any blatantly dumb errors!

Thx

 

[vb5prgrmr]

This is the VB forum there SQUASHJUNKIE. You may want to try the access forum or the office forum. You may want to also read FAQ222-2244 on how to get the most from this site, because it seems that you are using access/dao to access an external data source that does not seem to be another access database but seems to be a dBase/fox pro. The FAQ222-2244 if I remember correctly will suggest that you search first and if you are looking to connect to dBase/Fox Pro a search of this site will bring up a couple of examples for your connection string and a couple of threads that will point you to another site that has many examples of connection string.

Good Luck