I want to create new table in Access database during the runtime of my VB code. I am developing a "Library Management System" using VB6. At first i have used SQL statement to do the job. But it worked for once & then it stopped working. No error message was displayed. I am using "Microsoft ADO
Data Control 6.0 (OLEDB)" to create link with the database & the provider is
"Microsoft Jet 4.0 OLE DB Provider". I am using MS Access2003 to create the
database & VB6 with MSDN installed. I cannot continue the project without removing this problem. Please rescue me!!

I'm sure that you will first have to close the database. If you sift around for the methods of the control, there should be one for disconnecting or closing the connection to the database. Another option is to make the control a control array, when you are done with that particular connection, unload the control, and then load a new instance of it in the control array. Here is a link to working with Access Database from VB Code, and may be more beneficial to you:
http://www.timesheetsmts.com/adotutorial.htm

First of all you connect to database
then create recordset and attach the SQL Query to Recordsrt
SQL Query "Create Table " Use and open the Recordset you found that the table is created into your database
Read CReate table command in Acces Help

I want to create new table in Access database during the runtime of my VB code. I am developing a "Library Management System" using VB6. At first i have used SQL statement to do the job. But it worked for once & then it stopped working. No error message was displayed. I am using "Microsoft ADO
Data Control 6.0 (OLEDB)" to create link with the database & the provider is
"Microsoft Jet 4.0 OLE DB Provider". I am using MS Access2003 to create the
database & VB6 with MSDN installed. I cannot continue the project without removing this problem. Please rescue me!!

try this

Set database = workspace.CreateDatabase (name, locale, options)

look in the help section. make a search for "CreateDatabase". you will find all the information there.

sorry, that is for a dao database, i did not read it all

'set DAO refference for your project
' set dim var frist
Dim rs1 As Recordset
Dim db As Database
Dim td As TableDef
Dim f1 As Field
'------------------------------

Private Sub Form_Load()

'field variable

Dim iFields as string

' open your database hear

Set db = OpenDatabase("C:\temp.mdb")

' crate your table
Set td = db.TableDefs("Table1")

'Now that the database is created, add fields to the database

For iFields = 1 To 7 'The last number can be changed.
Set fl = td.CreateField("Field " & CStr(iFields), dbInteger)
td.Fields.Append fl
Next iFields

End Sub

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.