Multiple database

[rtvillan]

Can anyone help me with a code. I am using VB6 and need a code to automatically or dynamically load a new Access DB after a user creates it from a menu prompt I've coded:

Private Sub Command3_Click()
Dim newdata
' Dim lokatie$
Dim tb As TableDef
Dim fld As Field
Dim indx As Index
Dim DBNaam$
Dim dbNew As Database

'get name of the new database
newdata = InputBox("What's the name of the database?&quot
If newdata = "" Then Unload Form1

Screen.MousePointer = vbHourglass
'make database
DBNaam$ = newdata & ".mdb"
Set dbNew = CreateDatabase(DBNaam$, dbLangGeneral, dbVersion30)
'make a table
Set tb = dbNew.CreateTableDef("Table1&quot
'make a date-field
Set fld = tb.CreateField("Service Date", dbDate)
tb.Fields.Append fld
'make a meno-field
Set fld = tb.CreateField("Cost", dbCurrency)
tb.Fields.Append fld
'make a Yes/No field
Set fld = tb.CreateField("Odometer", dbBoolean)
tb.Fields.Append fld
'make a text field with a length of 8 characters
Set fld = tb.CreateField("Fuel Qty", dbBoolean)
tb.Fields.Append fld

Set fld = tb.CreateField("Service Type", dbText)
tb.Fields.Append fld
dbNew.TableDefs.Append tb



Screen.MousePointer = vbNormal

End Sub

At this point I have a database automatically loaded using ADO with a connection string : I need to have the program automatically load the newly created database and automatically modify the connection string.
Thanks.

 

[scarfhead]

TableDefs are DAO objects. To create new tables with ADO, you need to use ADOX. It should be in your references under "Microsoft ADO Ext. 2.6 for DDL and Security"

In Chapter 14 of Sam's "Teach Yourself ADO 2.5 in 21 Days" (the only reference I could find that covered ADOX)

sub main()
Dim MyDB as ADOX.Catalog
Dim MyTbl as ADOX.Table

set MyDB = CreateJetCatalog("C:\MyData.mdb)
set MyTbl = CreateYourTable
MyDB.Tables.Append MyTbl
End Sub

Function CreateJetCatalog(ByVal strFIlename as String) _
as ADOX.Catalog

Dim cat As New ADOX.Catalog

cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilename

Set CreateJetCatalog = cat
End Sub

Sub CreateYourTable() as ADOX.Table
Dim tbl as ADOX.Table

Set tbl = New ADOX.Table
tbl.Columns.Append "Service Date", adDate
tbl.Columns.Append "Cost", adCurrency
tbl.Columns.Append, "Odometer", adBoolean
tbl.Columns.Append, "Fuel Qty", adBoolean
tbl.Columns.Append, "Service Type", adVarChar, 96

set CreateYourTable = tbl
End Sub

What I'm not sure about is whether this creates a fully functional .mdb file. You may need to tap into the MS Access Object Library.

scarfhead