create table in ms access dynamically

[dhanya_piit]

i want to create table in ms acces database during run time.........
Dim myConnection As ADODB.Connection
Dim Conn As String
Dim vtblNametxt As String

Private Sub cratblebtn_Click()
vtblNametxt = tblNametxt.Text
myConnection.Execute ("create table " & vtblNametxt & " (id varchar2(3),pname varchar2(20), qtyp number(4));")
End Sub

Private Sub Form_Load()
Set myConnection = New ADODB.Connection
myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\\database.mdb"
myConnection.Open
End Sub
this is the code snippet that am using
am getting syntax error in crate table satement
plz help me out

[QVeen72]

Hi,

Take a Look at this thread:

http://www.daniweb.com/forums/thread65252.html

Regards
Veena

[dhanya_piit]

hey veena thanx
that really helps
but actually i want to know wheter the piece of code snippet that i posted is correct or is it wrong
cant i create a table in exsisting database at runtime using create table command and ms access as backend
plz reply........

[QVeen72]

Hi,

CREATE Table DDL is Enabled for Access 2K and higher..
Also note :
use Text instead of Varchar2
use Long instead on Number

some thing like this :
myConnection.Execute ("create table " & vtblNametxt & " (id Text(3), pname Text(20), qtyp Long)")

REgards
Veena

[dhanya_piit]

hey veena thanx........it helped :)

[dhanya_piit]

the syntax is creating table properly but when i give ame of teh table with a space in betwee it gives syntax error..........like if i create table with name"hello" it works fine bt when i use"hello world" it gives error..........can neone resolve this plz.....

[aktharshaik]

enclose the table name in []

like

[hello world]

hope it solves ur problem

Regards
Shaik Akthar

[dhanya_piit]

hey thanks athar........it worked........:)

[kritiga]

hi...
im having the same problem..i didnt understand using [] solved ur problem but didnt work for me...could u kindly explain..

[Emil_9]

i have this code worked for me. thanks for the idea.

Sub createTblOnTheFly()
Dim strSQL As String
strSQL = " CREATE TABLE TimeRecord (IDNo Text(30), DTR_Date datetime, TIME_IN datetime, TIME_OUT datetime, LB_IN datetime, LB_OUT datetime, CB_IN datetime, CB_OUT datetime)"

Dim myConnection As ADODB.Connection
Set myConnection = New ADODB.Connection
myConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & "C:\Users\efgarcia\Documents\database2.accdb;Persist Security Info=False;"
myConnection.Open

Dim NewTableName As String
'NewTableName = InputBox("What name do you want to give the table?")

'Check presence of table --------------
Dim rsSchema As ADODB.Recordset
Set rsSchema = New ADODB.Recordset


Set rsSchema = _
    myConnection.OpenSchema(adSchemaColumns, _
                    Array(Empty, Empty, "TimeRecord", Empty))
If rsSchema.BOF And rsSchema.EOF Then
    MsgBox "Table does not exist"
    myConnection.Execute strSQL, dbFailOnError
Else
    MsgBox "Table exists"
End If
rsSchema.Close
Set rsSchema = Nothing
'--------------------------------------

'DBEngine(0)(0).Execute strSQL, dbFailOnError
'DoCmd.RunSQL strSQL

End Sub