VB.NET Create FOREIGN KEY in existing table in Access using SQL?

Question

I've been having issues with creating relationships programatically in VB.NET for a piece of coursework. I keep getting the error of "Relationship must be on the same number of fields with the same data types." despite the fact that it is an AUTOINCREMENT into a NUMBER. Here is the code which i am using, bearing in mind that it creates the table just fine. Any help will be much appreciated.

CODE:

    Dim con As OleDbConnection

    Dim command As OleDbCommand

    Dim cat As New Catalog
    If My.Computer.FileSystem.FileExists("C:\Users.......\TEST.accdb") = False Then
        Dim str(3) As String
        cat.Create("Provider=microsoft.jet.oledb.4.0;Data Source = C:\Users........\TEST.accdb;jet oledb:engine type=5")
        cat = Nothing
        con = New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source =C:\Users.......TEST.accdb")
        con.Open()
        str(0) = "CREATE TABLE [tbl_Student] ([StudentID] AUTOINCREMENT NOT NULL PRIMARY KEY, [CourseID] NUMBER)"
        str(1) = "CREATE TABLE [tbl_Course] ([CourseID] AUTOINCREMENT NOT NULL PRIMARY KEY)"
        For i = 0 To 1
            command = New OleDbCommand(str(i), con)
            command.ExecuteNonQuery()
        Next
        con.Close()

        con = New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source =C:\Users........TEST.accdb")
        con.Open()
        Try
            str(3) = "ALTER TABLE [tbl_Student] ADD FOREIGN KEY (CourseID) REFERENCES tbl_Course(CourseID)"
            command = New OleDbCommand(str(3), con)
            command.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ErrorToString)
        End Try
        con.Close()
    End If

Andre · Accepted Answer · 2015-10-01 09:39:35Z

1

NUMBER isn't a defined data type for CREATE TABLE in Access. I'm surprised it works at all.

Use LONG instead.

See Microsoft Access Data Types (column "Data type (CREATETABLE)")

answered Oct 1, 2015 at 9:39

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GarethD Over a year ago

NUMBER Is a synonym for FLOAT, that is why it works in the create table

D.A Over a year ago

Thanks so much, to be honest I was also suprised that NUMBER worked. That really does explain a lot. Thanks so much! :D

Collectives™ on Stack Overflow

VB.NET Create FOREIGN KEY in existing table in Access using SQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related