Unable to link a Primary Key to a Foreign Key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Unable to link a Primary Key to a Foreign Key

Author

Topic

JohnTReed
Starting Member

2 Posts

Posted - 2009-06-07 : 10:37:14

I am creating an Access database from excel. In the VBA code I get the Database and Tables name from Excel spread sheet and assign them to variables, "tblNamePrime" As String, "tblName" As a String.

In the first table, “& tblNamePrime &”, I create a Primary Key called, [PrimeRecId] and this works ok. I create a second table, “& tblName &” (Note:“& tblName &” Contains a new table name) and try to create a Foreign Key called, [RecId] and try to REFERENCE it to the table, “& tblNamePrime &” [PrimeRecId].

I always receive the following error message:
Syntax Error in CONSTRAINT Clause
-2147217900(80040e14)

If I run the Sub without the Constraint ststements the routine works ok. However I have no linkage between the 2 tables therefore when you greate a Query to bring data you need go through the Reference process.

Below are the 2 statements in the code that if I remove them the program works fine and cretes the Tables.
'strSQL = strSQL & "CONSTRAINT [RecId]FOREIGN KEY, "
'strSQL = strSQL & "REFERENCES " & tblNamePrime & "[PrimeRecId], "

I have tried many different ways and have followed instructions from SQL, Access VBA and I am unable to Link the Primary Key to the Foreign Key.

The code below is just 2 tables but when I am done there will be a total of 4 tables all linked back to the first table. The Primary Key [PrimeRecId] will point to Foreign Key[RecId.

Thank you in advance for your help

Private Sub RDCModel()
Dim dbConnectStr As String
Dim Catalog As Object
Dim cnt As ADODB.Connection
Dim dbPath As String
Dim tblNamePrime As String
Dim tblName As String

'Set database name here
'MsgBox "Current user is " & Application.UserName
dbPath = ActiveSheet.Range("C1").Value 'Database Name

tblNamePrime = ActiveSheet.Range("C2").Value 'Table Name
tblName = ActiveSheet.Range("E2").Value 'Table Name

dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & " ;"

'MsgBox "You have " & dbConnectStr & " Database"
'Create new database
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.CREATE dbConnectStr
Set Catalog = Nothing
'Connect to database and insert a new table [AccountInformation]
Set cnt = New ADODB.Connection
With cnt
.Open dbConnectStr
.Execute "CREATE TABLE " & tblNamePrime & "([PrimeRecId]INTEGER IDENTITY(1,1) PRIMARY KEY, " & _
"[Account] text(15) WITH Compression, " & _
"[Account_Name] text(15) WITH Compression, " & _
"[Avg_Daily_Deposits] text(13) WITH Compression, " & _
"[Number_of_Locations] decimal(13,2));"
End With
Set cnt = Nothing

tblName = ActiveSheet.Range("E2").Value 'Table Name [FloatInformation]
Set cnt = New ADODB.Connection
With cnt
.Open dbConnectStr
strSQL = "CREATE TABLE " & tblName & "([RecId]INTEGER, "
strSQL = strSQL & "CONSTRAINT [RecId]FOREIGN KEY, "
strSQL = strSQL & "REFERENCES " & tblNamePrime & "[BkRecId], "
strSQL = strSQL & "[Float_Segment] text(15) WITH Compression, "
strSQL = strSQL & "[Price_Segment] text(15) WITH Compression, "
strSQL = strSQL & "[Product] text(13) WITH Compression, "
strSQL = strSQL & "[Avg_Daily_Items] text(13) WITH Compression, "
strSQL = strSQL & "[Avg_Daily_Dollars] text(13) WITH Compression, "
strSQL = strSQL & "[Avg_Daily_Transit_Dollars] text(13) WITH Compression, "
strSQL = strSQL & "[Percent_Transit] text(13) WITH Compression, "
strSQL = strSQL & "[Percent_Local] text(13) WITH Compression, "
strSQL = strSQL & "[Percent_NonLocal] text(13) WITH Compression, "
strSQL = strSQL & "[Monthly_Maintenance] decimal(13,2));"
.Execute strSQL
End With

Set cnt = Nothing
End Sub

John Reed

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-07 : 11:18:58

This
'strSQL = strSQL & "CONSTRAINT [RecId]FOREIGN KEY, "
'strSQL = strSQL & "REFERENCES " & tblNamePrime & "[PrimeRecId], "
gives
CONSTRAINT [RecId]FOREIGN KEY, REFERENCES " & tblNamePrime & "[PrimeRecId],
I think that comma after FOREIGN KEY is waste.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources