How to create an access database?

[Dr_Michael]

I have the recordset with the data, and I want to create on he fly an access database (on app.path) with one table which will contain the entire recordset. How?

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

[Lothar Haensler]

if you use ADO you can add a reference to ADOX = Microsoft ADO Ext 2.1 for DDL and Security.
it has methods for creating a database and objects inside...

[Lothar Haensler]

just found some sample code


Dim c as ADOX.Catalog
set c = new ADOX.Catalog
c.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=m:\schrott\test2.mdb"

[Dr_Michael]

...here is a routine that is close to what I want:


Dim appAccess as Access.Application
public Sub NewAccessDatabase()
Dim objCurrentDB as Object, objCurrentTableDef as Object, CurrentField as Variant
Const lngDB_Text as Long = 10
Const intFieldLength as Integer = 40
' Create new instance of Microsoft Access
set appAccess = CreateObject("Access.Application.9")
' Open database in Microsoft Access window
appAccess.NewCurrentDatabase App.Path & "\test.mdb"
' get Database object variable
set objCurrentDB = appAccess.CurrentDb
' Create new table
set objCurrentTableDef = objCurrentDB.CreateTableDef("TestTable")
' Create field in new table
set CurrentField = objCurrentTableDef.CreateField("Field0", lngDB_Text, intFieldLength)
' Append Field and TableDef objects
objCurrentTableDef.Fields.Append CurrentField
objCurrentDB.TableDefs.Append objCurrentTableDef
set appAccess = nothing
End Sub




It works! It creates the mdb file and one table.
I have a recordset: rsTempRec
Please if you can, modify the routine above in order to fill the table with contents of rsTempRec
Thanx!!!

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

[Lothar Haensler]

well, I'd just create a new recordset

dim rsnew as adodb.recordset
rsnew.open yourtablename, yourConnection
rstemp.movefirst
do while not rstemp.eof
rsnew.addnew
rsnew.field(0).value = rstemp.fields(1).value
...
rsnew.update
rstemp.movenext
loop
rstemp.close
rsnew.close



(this is Pcode!)

[Dr_Michael]

why to create a new recordset?
If we need a recordset same exactly with the first, can we use the clone property of the first recordset?
And how do I insert the new recordset in the table I created???

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

[Lothar Haensler]

how do you insert...?
rsNew takes your tablename as Source argument. That's how it is connected to your new table.

why not use clone?
because, I have never used it before - not a good reason I admit :-)

[Dr_Michael]

The initial recordset (rsTemp) has a lot of columns and rows. That means that I would like the table that I create to has exactly the same structure with the recordset (equal number of rows and columns, etc...)
Is it automatic???

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

[Lothar Haensler]

how about throwing away all your VB code and use SQL server 7's DTS (data transformation service) facility?
or bulkcopy out your sQLServer out to a textfile and import it into access using Access's Import feature?
I'd never write a single line of code for data transfer purposes!

[Dr_Michael]

Because my database contains data for 500 customers and I want to create 500 mdb's for each customer. That seems to be difficult by importing from Access. About the SQL utility you said, I will take a look. thanx anyway for all replies (and speed!!!)

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece