[Zeallord]

Hi I have problem with creating a table for MICROSOFT ACCESS using VB 6.0
Here is the coding.

' MS Access related data
Dim MyADOConn As ADODB.Connection
Dim MyADOCmd As ADODB.Command
Dim MyADORst As ADODB.Recordset
Dim MySQL As String

' This Variables is to record the Date and Time
' It is to be used for create ProductionRecord + Date table
Dim TodayTime As Date
Dim TodayDate As Date
Dim DateString As String
Dim TimeString As String

	TodayDate = Format(Now(), "dd-mmm-yy")
	DateString = CStr(TodayDate)
	DateString = Left(DateString, 2) & Mid(DateString, 4, 2) & Right(DateString, 2)

	Set MyADOConn = New ADODB.Connection
	MyADOConn.ConnectionString = "Provider = Microsoft.jet.oledb.4.0;" _
			& "Data Source = C:\XXX\DB\XXX.mdb"
   
	MyADOConn.Open
	
	Set MyADOCmd = New ADODB.Command
	Set MyADOCmd.ActiveConnection = MyADOConn
	MyADOCmd.CommandText = adCmdText

	MySQL = "CREATE TABLE PRODUCTIONREPORT" & DateString _
		& " (ProductionID   TEXT(50)CONSTRAINT PRODUCTIONID_PK PRIMARY KEY," _
		& " Drop		NUMBER(LONG INTEGER), " _
		& " SetMinWeight	NUMBER(DOUBLE), " _
		& " SetMaxWeight	NUMBER(DOUBLE), " _
		& " EntryTime	   DATE/TIME, " _
		& " Date			DATE/TIME, " _
		& " Weight		  NUMBER(DOUBLE) "
	
	
	MyADORst.Open MySQL, MyADOConn, adOpenDynamic, adLockOptimistic, adCmdText
	MyADORst.Update

When i run it, it prompt out run time error.. and saying that syntax error in field definition. When i go for debug it highlighted the code at MYADORST.Open ...........

Is the error caused by the datatype declaration error? or something else? Can anyone help me please.... Thanks in Advance

[reyben_89]

Zeallord, on 12 Dec, 2008 - 07:01 PM, said:

Hi I have problem with creating a table for MICROSOFT ACCESS using VB 6.0
Here is the coding.

' MS Access related data
Dim MyADOConn As ADODB.Connection
Dim MyADOCmd As ADODB.Command
Dim MyADORst As ADODB.Recordset
Dim MySQL As String

' This Variables is to record the Date and Time
' It is to be used for create ProductionRecord + Date table
Dim TodayTime As Date
Dim TodayDate As Date
Dim DateString As String
Dim TimeString As String

	TodayDate = Format(Now(), "dd-mmm-yy")
	DateString = CStr(TodayDate)
	DateString = Left(DateString, 2) & Mid(DateString, 4, 2) & Right(DateString, 2)

	Set MyADOConn = New ADODB.Connection
	MyADOConn.ConnectionString = "Provider = Microsoft.jet.oledb.4.0;" _
			& "Data Source = C:\XXX\DB\XXX.mdb"
   
	MyADOConn.Open
	
	Set MyADOCmd = New ADODB.Command
	Set MyADOCmd.ActiveConnection = MyADOConn
	MyADOCmd.CommandText = adCmdText

	MySQL = "CREATE TABLE PRODUCTIONREPORT" & DateString _
		& " (ProductionID   TEXT(50)CONSTRAINT PRODUCTIONID_PK PRIMARY KEY," _
		& " Drop		NUMBER(LONG INTEGER), " _
		& " SetMinWeight	NUMBER(DOUBLE), " _
		& " SetMaxWeight	NUMBER(DOUBLE), " _
		& " EntryTime	   DATE/TIME, " _
		& " Date			DATE/TIME, " _
		& " Weight		  NUMBER(DOUBLE) "
	
	
	MyADORst.Open MySQL, MyADOConn, adOpenDynamic, adLockOptimistic, adCmdText
	MyADORst.Update

When i run it, it prompt out run time error.. and saying that syntax error in field definition. When i go for debug it highlighted the code at MYADORST.Open ...........

Is the error caused by the datatype declaration error? or something else? Can anyone help me please.... Thanks in Advance

i'm not sure but i never think that the access had a console to make database like the mysql
mysql has a built in function where you can create and execute a query like you specify before.
but i never tried it into access to create table by create command

[thava]

seems you are not closing the btackets

seems you are not closing the brackets

[Zeallord]

Thanks Reyben_89 and Thava for replying my post. Regarding the closing bracket, yup it seemed that i missed one.. But still eventhough i have added the closing bracket, it still prompt me the same error..

saying runtime error - #some long number# with the error description, Syntax error in field definition...

Did i declare wrongly the datatype for the MS Access? I have already checked the RDO tutorial regarding the access datatype.. but still i can't get it right. Anyone, please can help me??

[thava]

if you can use the dao it's a simple one in access but just wait i would bring you a way to add a table

[Zeallord]

I finally know that It is not the code that have problem but it is the Microsoft Jet Database Engine do not support CREATE TABLE. No Choice but have to use DAO to CREATE the table. Thks guys for the help..

Btw, This info regarding that Microsoft Jet Engine do not support Create Table can be find at MS Access help file.. just search CREATE TABLE, and it will tell this

" Note The Microsoft Jet database engine does not support the use of CREATE TABLE, or any of the DDL statements, with non-Microsoft Jet database engine databases. Use the DAO Create methods instead."