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[i].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
--
"The most amazing achievement of the computer software industry is
its continuing cancellation of the steady and staggering gains
made by the computer hardware industry..." -- Henry Petroski
--
Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

You can create the file with the Office bits. If you want to avoid this (as
I have in the past), create an empty shell MDB file and you can then run
your DDL commands against it. If the database "created" always has certain
tables and seed values, I would make the "shell" with those tables and that
info already in it.

To do this, copy the MDB file (shell) to a folder and run the DDL against it
to finish set up.

This only answers part of your issue, of course, but you can add this method
to the case where the file is missing or wrong, etc.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************
"Frnak McKenney" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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[i].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
> --
> "The most amazing achievement of the computer software industry is
> its continuing cancellation of the steady and staggering gains
> made by the computer hardware industry..." -- Henry Petroski
> --
> Frank McKenney, McKenney Associates
> Richmond, Virginia / (804) 320-4887
> Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

I would also consider using SQL Server Compact edition for this task. It's
far easier to use permits you to create databases with a single line of
code. No, this is not another "SQL Server" binary but an evolution of SQLCe
and SQL Mobile that's been ported and expanded to work on the desktop. It's
been fully integrated into Visual Studio and ADO.NET Sync Services.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Frnak McKenney" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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[i].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
> --
> "The most amazing achievement of the computer software industry is
> its continuing cancellation of the steady and staggering gains
> made by the computer hardware industry..." -- Henry Petroski
> --
> Frank McKenney, McKenney Associates
> Richmond, Virginia / (804) 320-4887
> Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

Hi. Thanks for responding. I'm sorry it took me so long to get back
to you.

On Mon, 29 Oct 2007 10:21:31 -0500, Cowboy (Gregory A. Beamer) <(E-Mail Removed)> wrote:
>You can create the file with the Office bits.

Um... did you have any particular "bits" in mind?

> ... If you want to avoid
>this (as I have in the past), create an empty shell MDB file and you
>can then run your DDL commands against it. If the database
>"created" always has certain tables and seed values, I would make
>the "shell" with those tables and that info already in it.

Sounds good. Up until now, all the databases (not many) have been
manually created using COPY or a GUI equivalent; this would simply
automate the process.

The "down" side, of course, is that if the "prototype" file goes
missing the user will need to recover it from some place. Hm... I'm
using an "MSI" install, so presumably he/she/it could simply run an
Add/Remove Programs "Repair" operation to recover it. (Note to self:
_Test_ this! <grin>)

>To do this, copy the MDB file (shell) to a folder and run the DDL
>against it to finish set up.
>
>This only answers part of your issue, of course, but you can add
>this method to the case where the file is missing or wrong, etc.

Well, if I can't create a new one "from scratch", a missing file
will present some problems, yes? <grin!>

Thanks again for the suggestion.


Frank
--
Cats are smarter than dogs. You can't get eight cats to
pull a sled through snow. -- Jeff Valdez
--
Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

Bill,

Thanks for replying. I had planned to get back to you earlier, but,
well, ...

On Mon, 29 Oct 2007 10:11:42 -0700, William Vaughn <(E-Mail Removed)> wrote:
>
>I would also consider using SQL Server Compact edition for this
>task. It's far easier to use permits you to create databases with a
>single line of code. No, this is not another "SQL Server" binary
>but an evolution of SQLCe and SQL Mobile that's been ported and
>expanded to work on the desktop. It's been fully integrated into
>Visual Studio and ADO.NET Sync Services.

Anything approaching "real SQL" would appear to be an improvement
over Access/Jet data storage. However...

What I'm dealing with here is a small, dedicated-purpose progress
tracking application -- think of it as a special-purpose Information
Manager. It is intended for installation and unsupervised use by
people who only occasionally use their computers, and who are likely
to avoid calling in their organization's IT staff unless Absolutely
Necessary. "Access/.mdb file == My Data" is an extremely clear
paradigm, one that doesn't require much explanation, but if I could
get the same effect with somethign else I'm interested.

Do you know if SQLSCE requires any kind of "user maintenance", say
similar to the need to periodically "compress" an Access2000
database?

How does it relate to the "SQL Server Developer Edition"?

One thing of concern is that while it looks like SQLSCE doesn't
abolutely _require_ Visual Studion 2005, it does look like you lose
a lot of SQLSCE's benefit if you aren't using it (e.g. No VS Tools
for SQLSCE). In my case, I'm using C#.NET/2003, so I'd need to
upgrade.

Per the MS product pages, I'm eligible for the Upgrade package, and
upgrading makes sense, but I didn't see a retail package that
included VS2005 SP1, so I'd need to download and install that. So
far, not unreasonable (assuming the SP1 download doesn't require the
infamous MS "Are You Legal?" checks, which don't seem to work on my
MSWin2K "will touch the Internet over my cold, dead body"
development machine).

My impression -- please correct me if I'm wrong -- is that VS2005
apps will require .NET2.0 If so, do you know if this can be deployed
to Win2K machines? Some of the places this app might run haven't
moved up to XP yet.

Am I starting to sound like a Grumpy Old Man? Probably. <grin!>

All my customer wants is an app that can be installed and will run
without giving her too much grief, for a reasonable cost and in a
reasonable time. Me, I have to worry about stuff like getting
VS2005 running and then, after several days, suddenly discovering
that I'm producing code that half my sites can't run. Upgrades can
be wonderful things... but only after you've successfully upgraded
and everything runs at least as well as before.

On the good side, at least I don't have to do an MVT 21.8 SYSGEN as
part of the upgrade process. <grin!>

Thanks again for the feedback.


Frank
--
"What one writer can make in the solitude of one room is something
no power can easily destroy." -- Salman Rushdie
--
Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

On 03/11/2007 in message <(E-Mail Removed)> Frnak
McKenney wrote:

>Anything approaching "real SQL" would appear to be an improvement
>over Access/Jet data storage. However...

One thing to think about is your backup strategy. With an Access file it
is pretty easy to copy. Data files from apps like MySQL/SQL Server etc.
can be very difficult to copy unless you include a process for stopping
the server and re-starting it afterwards.

--
Jeff Gaines

On Sat, 03 Nov 2007 02:21:48 -0700, Jeff Gaines <(E-Mail Removed)> wrote:
> On 03/11/2007 in message <(E-Mail Removed)> Frnak
> McKenney wrote:
>
>>Anything approaching "real SQL" would appear to be an improvement
>>over Access/Jet data storage. However...
>
>One thing to think about is your backup strategy. With an Access
>file it is pretty easy to copy. Data files from apps like
>MySQL/SQL Server etc. can be very difficult to copy unless you
>include a process for stopping the server and re-starting it
>afterwards.

Jeff,

Thanks for the reminder. Whether the individual user runs his own
backups or an IT department does it "invisibly in the background",
for small, single-user applications the paradigm of "this file == my
data" gives the user a simple way of determining what to back up, or
what to ask "the IT guys" to restore.


Frank
--
Timing is critical to the outcome of a rain dance.
--
Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

Frnak,
You definitely need to download my Ebook. No, SQLCe does NOT require
periodic maintenance like JET. It can be passed around as a file like JET
and backed up in a similar fashion (unlike JET). It's ideal for single user
applications. Consider that this database is also fully encryptable--a
feature not in JET or in SQL Server until 2008. It supports a full SQL
syntax, a subset of the datatypes and a simpler, low-impact approach to data
access.
Feel free to contact me directly if you have more questions. Clearly,
there is some confusion about it in the newsgroups.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Frnak McKenney" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bill,
>
> Thanks for replying. I had planned to get back to you earlier, but,
> well, ...
>
> On Mon, 29 Oct 2007 10:11:42 -0700, William Vaughn
> <(E-Mail Removed)> wrote:
>>
>>I would also consider using SQL Server Compact edition for this
>>task. It's far easier to use permits you to create databases with a
>>single line of code. No, this is not another "SQL Server" binary
>>but an evolution of SQLCe and SQL Mobile that's been ported and
>>expanded to work on the desktop. It's been fully integrated into
>>Visual Studio and ADO.NET Sync Services.
>
> Anything approaching "real SQL" would appear to be an improvement
> over Access/Jet data storage. However...
>
> What I'm dealing with here is a small, dedicated-purpose progress
> tracking application -- think of it as a special-purpose Information
> Manager. It is intended for installation and unsupervised use by
> people who only occasionally use their computers, and who are likely
> to avoid calling in their organization's IT staff unless Absolutely
> Necessary. "Access/.mdb file == My Data" is an extremely clear
> paradigm, one that doesn't require much explanation, but if I could
> get the same effect with somethign else I'm interested.
>
> Do you know if SQLSCE requires any kind of "user maintenance", say
> similar to the need to periodically "compress" an Access2000
> database?
>
> How does it relate to the "SQL Server Developer Edition"?
>
> One thing of concern is that while it looks like SQLSCE doesn't
> abolutely _require_ Visual Studion 2005, it does look like you lose
> a lot of SQLSCE's benefit if you aren't using it (e.g. No VS Tools
> for SQLSCE). In my case, I'm using C#.NET/2003, so I'd need to
> upgrade.
>
> Per the MS product pages, I'm eligible for the Upgrade package, and
> upgrading makes sense, but I didn't see a retail package that
> included VS2005 SP1, so I'd need to download and install that. So
> far, not unreasonable (assuming the SP1 download doesn't require the
> infamous MS "Are You Legal?" checks, which don't seem to work on my
> MSWin2K "will touch the Internet over my cold, dead body"
> development machine).
>
> My impression -- please correct me if I'm wrong -- is that VS2005
> apps will require .NET2.0 If so, do you know if this can be deployed
> to Win2K machines? Some of the places this app might run haven't
> moved up to XP yet.
>
> Am I starting to sound like a Grumpy Old Man? Probably. <grin!>
>
> All my customer wants is an app that can be installed and will run
> without giving her too much grief, for a reasonable cost and in a
> reasonable time. Me, I have to worry about stuff like getting
> VS2005 running and then, after several days, suddenly discovering
> that I'm producing code that half my sites can't run. Upgrades can
> be wonderful things... but only after you've successfully upgraded
> and everything runs at least as well as before.
>
> On the good side, at least I don't have to do an MVT 21.8 SYSGEN as
> part of the upgrade process. <grin!>
>
> Thanks again for the feedback.
>
>
> Frank
> --
> "What one writer can make in the solitude of one room is something
> no power can easily destroy." -- Salman Rushdie
> --
> Frank McKenney, McKenney Associates
> Richmond, Virginia / (804) 320-4887
> Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)