F
Frnak McKenney
I'm making changes to a small, portable, single-user database
application I wrote a couple of years back built around a muli-table
Access database and written in C# and .NET 1.1. Most of the changes
are are minor, but one involves "subsetting" the database, that is,
creating a new database file (.mdb) with a subset of the current
database's rows. Read in a schema and some tables from one database
/ file, write them out to a brand new database / file. Right?
Well, either I'm being incredibly dense, or this is an extremely
non-trivial task. Since it's difficult for me to imagine that
'CREATE' support wouldn't be hidden _somewhere_ in .NET's OleDb
support, I'm hoping that if I describe what I've tried someone will
put me out of my misery with a few, well-chosen -- but helpful --
comments on my obtuseness. <grin?>
Reading tables from the original database ("Filling") into
DataTables in a DataSet is fairly straightforward, and the 'web
abounds with examples of how to do this. Creating a new DataSet
with a duplicate schema isn't hard, either:
DataSet dsClone = dsOriginal.Clone();
followed by selected row-copies from the old to the new via
DataViews:
dsClone.Tables[tname].ImportRow( dv.Row );
Then you open a connection to the new database-to-be:
OleDbConnection connDb = new OleDbConnection();
connDb.ConnectionString = DbConnString1
+ filename
+ DbConnString2;
connDb.Open();
Whoops!
- If the file name doesn't already exist, you get an exception.
- If the file exists but hasn't been Access-initialized, you get
an exception ("wrong file structure" or something similar).
So either there is a secret substring I need to include in my
ConnectionString ("...Mode=Create;..." ?) to prepare the file for
a CREATE-like operation, or the file structure has to be "built" /
initialized prior to opening the Connection.
There's got to be some step there that I'm overlooking.
Hints would be appreciated. If I can't figure out how to do this, my
fallbacks include:
1) Byte-copying the current .mdb file -- while it's open -- under a
new name, opening ("connecting to") the new file, and then
emptying some tables and chopping out rows from others (and
maybe doing a compress-in-place on the result),
2) Adding an Empty-Db.mdb file ("...just the Schema, Ma'am") to the
package that would only be used for subsetting the database,
following the steps in option (1), or
3) Reverse-engineering the .mdb file structure and coding the
creation of a new one "by hand". (No, I'm not serious -- or not
entirely serious-- about this one, just frustrated enough to
start thinking anlong those lines.)
4) Embedding a binary string containing a complete (but table-less)
.mdb file into my applic... no, let's not go there. (But it
_would_ be portable. <grin!>)
Please, someone, point out to me that I've been repeatedly reading
past an OleDbConnection.OpenNew() method that does exactly what I'm
trying to do... or something along those lines! <grin>
Frank McKenney
application I wrote a couple of years back built around a muli-table
Access database and written in C# and .NET 1.1. Most of the changes
are are minor, but one involves "subsetting" the database, that is,
creating a new database file (.mdb) with a subset of the current
database's rows. Read in a schema and some tables from one database
/ file, write them out to a brand new database / file. Right?
Well, either I'm being incredibly dense, or this is an extremely
non-trivial task. Since it's difficult for me to imagine that
'CREATE' support wouldn't be hidden _somewhere_ in .NET's OleDb
support, I'm hoping that if I describe what I've tried someone will
put me out of my misery with a few, well-chosen -- but helpful --
comments on my obtuseness. <grin?>
Reading tables from the original database ("Filling") into
DataTables in a DataSet is fairly straightforward, and the 'web
abounds with examples of how to do this. Creating a new DataSet
with a duplicate schema isn't hard, either:
DataSet dsClone = dsOriginal.Clone();
followed by selected row-copies from the old to the new via
DataViews:
dsClone.Tables[tname].ImportRow( dv.Row );
Then you open a connection to the new database-to-be:
OleDbConnection connDb = new OleDbConnection();
connDb.ConnectionString = DbConnString1
+ filename
+ DbConnString2;
connDb.Open();
Whoops!
- If the file name doesn't already exist, you get an exception.
- If the file exists but hasn't been Access-initialized, you get
an exception ("wrong file structure" or something similar).
So either there is a secret substring I need to include in my
ConnectionString ("...Mode=Create;..." ?) to prepare the file for
a CREATE-like operation, or the file structure has to be "built" /
initialized prior to opening the Connection.
There's got to be some step there that I'm overlooking.
Hints would be appreciated. If I can't figure out how to do this, my
fallbacks include:
1) Byte-copying the current .mdb file -- while it's open -- under a
new name, opening ("connecting to") the new file, and then
emptying some tables and chopping out rows from others (and
maybe doing a compress-in-place on the result),
2) Adding an Empty-Db.mdb file ("...just the Schema, Ma'am") to the
package that would only be used for subsetting the database,
following the steps in option (1), or
3) Reverse-engineering the .mdb file structure and coding the
creation of a new one "by hand". (No, I'm not serious -- or not
entirely serious-- about this one, just frustrated enough to
start thinking anlong those lines.)
4) Embedding a binary string containing a complete (but table-less)
.mdb file into my applic... no, let's not go there. (But it
_would_ be portable. <grin!>)
Please, someone, point out to me that I've been repeatedly reading
past an OleDbConnection.OpenNew() method that does exactly what I'm
trying to do... or something along those lines! <grin>
Frank McKenney