Best Practices: Creating an Access Database at Runtime

I'm trying to create an Access Database at runtime which will contain two tables: Users and Transactions. I've gotten so far as to use the ADOX.CatalogClass to create the empty database. Now, for creating the tables, I see two options: use SQL or a DataSet to create the tables. There may be other options, and I'm open to them, of course.

I'd tried keeping a binary copy of a skeleton database file compiled into my application, but that just doesn't seem right...

Can someone provide input on the best approach for this Using the DataSet is certainly more progromatic, but using SQL seems easier. Thanks!

Answer this question

Best Practices: Creating an Access Database at Runtime

Chris.Stewart

hi,

i don't know how the dataset can create Access database,

yes you can use ADox to create the database you can also use (ADO or DAo i don't remember the name) to create tables or columns , but i don't recommend that at all

the best and easiest way to do that is to use skeleton as you said , but its also good to use sql to create tables

string connstr = @"provider=microsoft.jet.oledb.4.0;data source =C:\dateTesting.mdb";
string sqlstr = "create table FirtTable( field1 Integer primary key ,fied2 varchar(15))";
OleDbConnection conn = new OleDbConnection(connstr);
conn.Open();
OleDbCommand cmd = new OleDbCommand(sqlstr, conn);
cmd.ExecuteNonQuery();
conn.Close();

if you wanted to make your column autoincremente(identity, sequence, autonumber) they key word in access is counter so the previous sqlstr will be something like this

string sqlstr = "create table FirtTable( field1 COUNTER primary key,fied2 varchar(15))";

hope this helps

P R W
The answer is already there but... If you use ADOX, when you try to generate the installer, ... you are not allowed to do so. You MUST exclude the library that contains ADOX. The project is useless, unless you install the VB2005 in the client machine. Any sugestion
shohaib
If I understand you your wrong. You don't need to install ADOX on the client machine.

I installed my program that uses ADOX on a fresh system with just Windows XP OS and it installed fine. (It needed the .NET Framework 2.0, the Crystal Report for .NET Framework, and Windows installer 3.1 which it installed automatically.) It took about 10 minutes to install with a broadband connection to the internet.
mario.muja
In VB:

Dim conn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source =C:\testing.mdb")

conn.Open()

Dim cmd = New OleDbCommand("create table FirstTable( field1 counter primary key ,field2 varchar(15))", conn)

cmd.ExecuteNonQuery()

conn.Close()
Chris Lively
As an added requirement, one of the fields must be an AutoNumber Replication ID. As I've been looking around on the internet, perhaps the only way to accomplish this is to embed a skeleton into the program.
Best Practices: Creating an Access Database at Runtime

Answer this question