Creating Table in MS ACCESS 2003 database

[acidflash]

Hi everyone,

I am having some trouble creating a table in a MS access 2003 database. I have tried using adox to create it with catalogues and with adox.columns.append command, with no luck. I have also tried using oledb.oledbcommand with the sql command "CREATE TABLE" with no luck. Can anyone help? This is a bit urgent, thank you all in advance.

 

[JuggaloBrotha]

I use the sql Create Table command for this (adox is very outdated and I've ran into problems with it in .Net) what's the code you were using when you tried the create table sql?

 

[acidflash]

Dim Newtable as oledb.oledbcommand
dim sql as string
dim command as new oledb.oledbcommand
dim connection = new oledb.oledbconnectio("Provider=microsoft.jet.oledb.4.0;data source =" & my.application.info.directorypath & "\Yassen.mdb"
sql = "CREATE TABLE test ([Id] COUNTER, [FullName] TEXT(50))"
newtable = new oledb.oledbcommand(sql, connection)
connection.open()
command = new oledb.oledbcommand(sql, connection)
command.executenonquery()
connection.close()

 

[JuggaloBrotha]

First off, I don't think Access understands the data type 'Counter', how about you try something like this:

Dim myConnection As New OleDb.Connection("Provider=Microsoft.Jet.OLEDB.4.0;Mode=Share Deny None;Data Source=" & Application.StartupPath & "\Yassen.mdb")
Dim TheSql As String = "CREATE TABLE Test ([Id] INTEGER NOT NULL PRIMARY KEY, [FullName] Text(50));"

Try
    myConnection.Open()
    Dim DataCommand As New OleDb.OleDbCommand
    Dim myTrans As OleDb.OleDbTransaction

    DataCommand.Connection = m_Connection
    DataCommand.CommandText = TheSql

    myTrans = m_Connection.BeginTransaction
    DataCommand.Transaction = myTrans
    DataCommand.ExecuteNonQuery()
    myTrans.Commit()
Catch ex As Exception
    Throw New InvalidOperationException("Could not create the table" & Environment.NewLine & TheSql & Environment.NewLine & ex.ToString)
Finally
    myConnection.Close()
End Try

 

[acidflash]

Juggalo, I tried the code, but it did not work... any suggestions?

I have it inside a Private Function CreateTable() and when i press a button after doing some other stuff i call the function CreateTable() but it does not add the table to the database. what might be the problem? could it be the ; after text(50)) ?

Thanks in advance.

 

[JuggaloBrotha]

I think it's the Text(50) part, try VARCHAR(50) instead

 

[acidflash]

I changed it but still no luck; This is driving me nuts

 

[acidflash]

Juggalo,

I finally got it to work, thank you very much. The problem was with the function, when i put the code into a button it worked like a charm. I am having a small problem though changing the name "test" to another string. When the string is a single word without spaces it works fine, but when there is spaces it gives an error while creating the table. I said to myself why not trim the string, I tried that but whenever I trim the string from the start it returns nothing and when i trim it from the end it returns nothing aswell. This is the code I am using:

Dim strFullName as string
Dim Space as string
Dim TableName as string

strFullName = FullName.text
space = strfullName.trimstart(" "c) 'or space = strFullName.trimend(" "c)
TableName = strFullName.trim(space.tochararray())
msgbox("" & TableName)

if I put just " " as the value for space, I just get whatever is in Fullname.text as is, no trimming, please help.

 

[acidflash]

Ok I got it to work with strFullname.replace(" ","")

thank you everyone for your help.

 

[JuggaloBrotha]

Ah, you didn't mention that the table name can change, here's an example of how I handle this (I stripped out different table name stuff when I put together the previous example:

Private Sub CreateTable(ByVal TblName As String)
    Dim myConnection As New OleDb.Connection("Provider=Microsoft.Jet.OLEDB.4.0;Mode=Share Deny None;Data Source=" & Application.StartupPath & "\Yassen.mdb")
    Dim TheSql As String = "CREATE TABLE [" & tblName.Trim & "] ([Id] INTEGER NOT NULL PRIMARY KEY, [FullName] Text(50));"

    Try
        myConnection.Open()
        Dim DataCommand As New OleDb.OleDbCommand
        Dim myTrans As OleDb.OleDbTransaction

        DataCommand.Connection = m_Connection
        DataCommand.CommandText = TheSql

        myTrans = m_Connection.BeginTransaction
        DataCommand.Transaction = myTrans
        DataCommand.ExecuteNonQuery()
        myTrans.Commit()
    Catch ex As Exception
        Throw New InvalidOperationException("Could not create the table" & Environment.NewLine & TheSql & Environment.NewLine & ex.ToString)
    Finally
        myConnection.Close()
    End Try
End Sub