5 Replies - 42264 Views - Last Post: 26 December 2008 - 08:06 PM Rate Topic: -----

#1 Zeallord  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 12-December 08

How To Create Table in Access using VB 6.0

Post icon  Posted 12 December 2008 - 08:01 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: How To Create Table in Access using VB 6.0

#2 reyben_89  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 69
  • Joined: 30-November 08

Re: How To Create Table in Access using VB 6.0

Posted 13 December 2008 - 01:26 AM

View PostZeallord, 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
Was This Post Helpful? 1
  • +
  • -

#3 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: How To Create Table in Access using VB 6.0

Posted 14 December 2008 - 06:49 PM

seems you are not closing the btackets

seems you are not closing the brackets
Was This Post Helpful? 0
  • +
  • -

#4 Zeallord  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 12-December 08

Re: How To Create Table in Access using VB 6.0

Posted 14 December 2008 - 08:27 PM

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??
Was This Post Helpful? 0
  • +
  • -

#5 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: How To Create Table in Access using VB 6.0

Posted 18 December 2008 - 07:41 PM

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
Was This Post Helpful? 1
  • +
  • -

#6 Zeallord  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 12-December 08

Re: How To Create Table in Access using VB 6.0

Posted 26 December 2008 - 08:06 PM

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."
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1