How to Create a new Table in access using VB 2008 Express

[acxes]

Hello,

I am working on my new project and I need to create a table for each user dynamically. In which the user will provide the Table name in the textbox and on pressing Create button a new table gets created in the same MS access database file with the provided name.

Thanks in advance for your help.

here is some not working code which is not helpful to you guy at all:

Con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\test1.mdb"
    Con.Open()
    SQL =  "CREATE TABLE" + A + "([A Class] number(3))"
    Cmd = New OleDb.OleDbCommand(SQL, Con)
    'DA = New OleDb.OleDbDataAdapter(DT, Con)
    'DS.Tables.Add(A)
    'DA.Fill(DS, "Tester")
    'DT = New DataTable(A)
    'Dim TName As DataColumn = New DataColumn("Test1")
    Cmd.ExecuteNonQuery()
    DS.Tables.Add(A)
    DA.Update(DS, A)
    MsgBox("Table Created")

[Ramy Mahrous]

Can you create table in access by SQL script??

[acxes]

I guess Yes, because I'll able to manipulate and show table data in the VB form using SQL commands.

[Ramy Mahrous]

Don't guess please; try it in Access, and tell me the result!

[acxes]

Another option which is in my mind is creating DATATABLE object as you can see in the code with ('), I am trying that also but unfortunately not succeed :(

No, You cannot able to create a table using SQL in access

[acxes]

After 1/2 hour research I came to a concussion that We are not able to use CREATE TABLE command for MS Access database to create a table.

[Ramy Mahrous]

That's what I want to hear from you :) mark this thread as solved and write and hint it's impossible :)

[acxes]

This post is still open, as My logic/code is incorrect, but I still want to know How to Create a new Table in access, please suggest some way to create dynamic tables in the same file.

Thanks in Advance

[Ramy Mahrous]

In Microsoft Access you can't!

[rapture]

acxes are you getting instructions from a professor to do this or are you trying to do it on your own

[acxes]

Oooh, so we cannot create dynamic tables in MS access Ramy, if it is so then I close this thread.

I am trying to do it my own.

[Ramy Mahrous]

I said that in my 2nd reply, friend :) yes mark it as solved.

[rapture]

** I was just trying to figure out if you had an assignment and chose access when you needed something else.

Since it's not possible for what you wanted to do can you go ahead and mark it as solved now for others?

[samir_ibrahim]

Well, Any thing can be done by Foxpro can be done in vb.net also

Try this code

Dim oAccess As Object
        'Create Access Database
        oAccess = CreateObject("Access.Application")
        oAccess.NewCurrentdatabase("c:\myaccess.mdb")

        'Create Table
        Dim tdf As New DAO.TableDef
        Dim fld As New DAO.Field
        Dim db As Object
        db = oAccess.CurrentDb
        tdf = db.CreateTableDef("MyTable")
        'Create Field MyField as long
        Dim dbLong As Integer
        dbLong = 4
        fld = tdf.CreateField("MyField", dbLong)
        tdf.Fields.Append(fld)
        db.TableDefs.Append(tdf)
        oAccess.Visible = True

[Keith3232]

I may be missing something here, but if I understand the question correctly than yes you can. I am currently using code that creates several tables (with multiple fields) in an existing Access database. This is all done using a “CREATE TABLE” SQL statement. A quick glance at your code, and (assuming “A” is a string variable that will hold the user name and/or table name) I see a few simple errors. First, you need a space added before you add your table name. Also, it appears you are using the variable as a literal in the SQL statement as well. From what I can tell, looks like your statement should resemble this:

“CREATE TABLE “ & A & “ ([“ & A & “ Class] integer)”

instead of:

"CREATE TABLE" + A + "([A Class] number(3))"

If A = “John” then this would create a table in the database called “John” with a numeric field called “John Class”. I wished I would have found this thread earlier, and I am surprised it has not been pointed out before this. Hope you figured this out already, or at least it helps somebody down the road.

[UJNimz]

Hi, this is a code to create a table in a database.
database name= UJNIMZ
table name= YOU CAN ENTER TO TextBox1
Try it my friends...
thank you.

UJNimz
------------------------------------------------------------------------------------
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con As OleDbConnection
Dim com As OleDbCommand
Dim TABLE As String
TABLE = TextBox1.Text
Try
con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\New folder\UJNIMZ.accdb")
com = New OleDbCommand("CREATE TABLE " + TABLE + "(Index_No char(8),Entered_Date char(10),First_Name char(50),Last_Name char(60),Date_of_Birth date,Sex char(6),Parents_Name char(50),Job char(40),Tel_No char(11))", con)
con.Open()
com.ExecuteNonQuery()
MsgBox("Table Created", MsgBoxStyle.OkOnly)
con.Close()
Catch ex As Exception
MsgBox(ErrorToString, MsgBoxStyle.Exclamation)
End Try

End Sub

[UJNimz]

try this also
Imports System.Data.OleDb
Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con As OleDbConnection
Dim com As OleDbCommand
Dim tab As String
tab = ComboBox1.Text
MsgBox(tab)
Try
con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\New folder\VILASEE.accdb")
com = New OleDbCommand("CREATE TABLE " + tab + "(Index_No numeric(8,2),Entered_Date char(10),First_Name char(50),Last_Name char(60),Date_of_Birth date,Sex char(6),Parents_Name char(50),Job char(40),Tel_No char(11))", con)
con.Open()

com.ExecuteNonQuery()
MsgBox("Table Created", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Information")
con.Close()
Catch ex As Exception
MsgBox(ErrorToString, MsgBoxStyle.OkOnly + MsgBoxStyle.Exclamation, "Error")
End Try
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Me.Close()
End Sub
End Class

[aydinozdemir]

omg it is possible so why those access gurus are claiming that it isnt possible and forcing this guy to close the topic?