create table in access database using Adox in vb

Question

I'm using a Windows form application in Visual Basic 2012 to create a new Microsoft Access database using .ADOX. I can create the database but can't create a table in the database.

My code is :

Imports ADOX
Imports System.Data.OleDb
Public Class Form1
Dim mycommand As OleDbCommand
Dim myconnection As OleDbConnection
Dim myReader As OleDbDataReader
Dim str As String
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim cat As New Catalog()
    Dim tablename As String = "Users"
    cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\important\DDL.mdb;Jet OLEDB:Engine Type=5")
    cat = Nothing
    myconnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\important\DDL.mdb ")
    myconnection.Open()
    str = "CREATE TABLE [ " & tablename & "] ([Username] varchar(50)), ([Password] varchar(50)), ([E-mail] varchar(75))"
    mycommand = New OleDb.OleDbCommand(str, myconnection)
    mycommand.ExecuteNonQuery()
    MsgBox("Database created")
End Sub
End Class

The error is get is:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error in CREATE TABLE statement.

Any help is appreciated.

EDITED: The following code gets past the first error but i now get a field definition error, i know the code should work as it works with adding a number field, possible because the field type in access is short text and long text but anything i've tried doesn't seem to work.

Imports ADOX
Imports System.Data.OleDb
Public Class Form1
Dim mycommand As OleDbCommand
Dim myconnection As OleDbConnection
Dim myReader As OleDbDataReader
Dim str As String
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim cat As New Catalog()
    cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\important\DDL.mdb;Jet OLEDB:Engine Type=5")
    cat = Nothing
    myconnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\important\DDL.mdb ")
    myconnection.Open()
    str = "CREATE TABLE [Users] (Username Varchar(50), Password Varchar(50), E-mail Varchar(50))"

    mycommand = New OleDb.OleDbCommand(str, myconnection)
    mycommand.ExecuteNonQuery()
    MsgBox("Database created")
End Sub
End Class

I rolled the question back. Please do not change the original question — it nullifies any answers that were posted against that question. — LarsTech, Mar 13 '15 at 16:16

score 0 · Accepted Answer · answered Mar 13 '15 at 15:44

0

Looks like you have too many parenthesizes, but I think the actual error is coming from the leading space you have in your query:

                     V
str = "CREATE TABLE [ " & tablename & "]

Try changing it to:

str = "CREATE TABLE [" & tablename & "] ([Username] varchar(50), [Password] varchar(50), [E-mail] varchar(75))"

Make sure you dispose of your objects, preferably in a using block:

Example:

Using cn As New OleDb.OleDbConnection(mdb)
  cn.Open()
  Using cmd As New OleDb.OleDbCommand(str, cn)
    cmd.ExecuteNonQuery()
  End Using
End Using

answered Mar 13 '15 at 15:44

LarsTech

80,625
14
153
225

Thanks, i got the code working but i can't get it to put the field definition to be short text (but it does work if i put the field definition as number). any ideas? (i edited my question above with the new code im using) – Dan Mar 13 '15 at 16:12
1

@Dan You are missing the size, like in your original post: `varchar(50)`. – LarsTech Mar 13 '15 at 16:15
i've tried adding the size in as well but i still get a field definition error, could it be called something else instead of varchar? – Dan Mar 16 '15 at 12:47
@Dan Make sure you put those variable names in brackets `[Password]` is a keyword, so it must be bracketed. Putting a dash in your email field is a bad idea, just called it eMail. If you can't change it, you would have to put that in brackets, too: `[E-mail]` Your original post had those fields in brackets, so I don't understand why you would remove them. – LarsTech Mar 16 '15 at 14:36
Alright cheers, took them out because some other examples didn't have them in brackets but what you've said does make sense, thank you. I'll let you know if that fixes it – Dan Mar 16 '15 at 15:24

score 0 · Answer 2 · edited Jan 26 '21 at 19:37

At the end the code looks like this:

Imports ADOX
Imports System.Data.OleDb
Public Class Form1
    Dim mycommand As OleDbCommand
    Dim myconnection As OleDbConnection
    Dim myReader As OleDbDataReader
    Dim str As String
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim cat As New Catalog()
        cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\DDL.mdb;Jet OLEDB:Engine Type=5")
        cat = Nothing
        Using myconnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\DDL.mdb ")
            myconnection.Open()
            str = "CREATE TABLE [Users] ([Username] varchar(50), [Password] varchar(50), [E-mail] varchar(50))"

            Using mycommand As New OleDb.OleDbCommand(str, myconnection)
                mycommand.ExecuteNonQuery()
            End Using
        End Using
        MsgBox("Database created")
    End Sub
End Class

create table in access database using Adox in vb

2 Answers2