Setting Primary Key on a Linked SQL Table

[BruceF]

I am linking to SQL tables. On one of them, the "Select Unique Record
Identifier" dialog box comes up, and I have to choose one or more fields for
my primary key. How can I program this in VBA so that this step is done
automatically? If it helps, this is the line of code I use to link the table:
DoCmd.TransferDatabase acLink, "ODBC", strCon, acTable, "AttendanceCode",
"dbo_AttendanceCode", , True

Thank you.

Bruce

 

[Sylvain Lafontaine]

Hum, this should be done automatically, if I remember correctly. The first
thing to do would be to make sure that a primary key (or at least an unique
index?) has been defined for this table.

Also, with some older versions of Access, there is a problem if the name of
the primary key is not alphabetically the first one. This is way the
upsizing wizard will usually give a name such as aaaaaaPK_TheTable to the
primary key when it upsizes a table.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Stefan Hoffmann]

hi Bruce,

I am linking to SQL tables. On one of them, the "Select Unique Record
Identifier" dialog box comes up, and I have to choose one or more fields for
my primary key. How can I program this in VBA so that this step is done
automatically? If it helps, this is the line of code I use to link the table:
DoCmd.TransferDatabase acLink, "ODBC", strCon, acTable, "AttendanceCode",
"dbo_AttendanceCode", , True

You need to use the CreateTableDef method:

Public Function TableLinkODBC(ASourceName As String, _
Optional ADestinationName As String = "", _
Optional APrimaryKey As String = "") _
As Boolean

On Local Error GoTo LocalError

' Here you need your strCon.
Const CONNECTION_ODBC As String = "ODBC;" & _
"DRIVER={SQL Server};" & _
"SERVER=yourServer;" & _
"DATABASE=yourDatabase;" & _
"UID=user;" & _
"PWD=password"

TableLinkODBC = False

ASourceName = UCase(ASourceName)
If ADestinationName = "" Then
ADestinationName = ASourceName
End If

If TableExists(ADestinationName) Then
Debug.Print "-";
CurrentDbC.TableDefs.Delete ADestinationName
End If

Debug.Print "+"; ASourceName; "="; ADestinationName
CurrentDbC.TableDefs.Append _
CurrentDbC.CreateTableDef( _
ADestinationName, 0, ASourceName, CONNECTION_ODBC)
CurrentDbC.TableDefs.Refresh

If APrimaryKey <> "" Then
SQLExecute "CREATE INDEX pk_" & ADestinationName & " ON " & _
ADestinationName & "(" & APrimaryKey & ") WITH PRIMARY;"
End If

TableLinkODBC = True
Exit Function

LocalError:
MsgBox Err.Description

End Function


CurrentDbC is Michael Kaplan's solution, e.g.

http://access.joposol.com/accept/vba-makros/currentdb.html

TableExists() is a simple function checking the TableDefs collection, if
this table exists.

Specify the primary key fields as comma separated list, if the are not
automatically recognized.

mfG
--> stefan <--

 

[BruceF]

Thanks Stefan. I'm finally getting back to my project. If I have any
questions, I'll let you know.
Bruce

 

[BruceF]

Thanks Stefan. I'm finally getting back to my project. If I have any
questions, I'll let you know.
Bruce