Re: Creating a new Access database file (.mdb) in .NET/C#
- From: "William Vaughn" <billvaNoSPAM@xxxxxxxxx>
- Date: Mon, 29 Oct 2007 10:11:42 -0700
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" <frnak@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:13ibjsnicudog1a@xxxxxxxxxxxxxxxxxxxxx
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)
.
- References:
- Creating a new Access database file (.mdb) in .NET/C#
- From: Frnak McKenney
- Creating a new Access database file (.mdb) in .NET/C#
- Prev by Date: Re: How much of the functionality of SQL Server is mirrored in ADO.Net
- Next by Date: Re: How much of the functionality of SQL Server is mirrored in ADO.Net
- Previous by thread: Re: Creating a new Access database file (.mdb) in .NET/C#
- Next by thread: Re: How much of the functionality of SQL Server is mirrored in ADO.Net
- Index(es):
Relevant Pages
|
