MS Access DAO -> ADO.NET Migration

[moomoofatcow]

I have an VB6 program that I wrote that access Microsoft Access
databases using DAO. DAO has methods and properties that are Access
specific (such as viewing and editing relationships or indexes on the
tables) I want to write a new version of the program in VB 2005, and I
want to use ADO.NET to access the data. Are there ways of changing the
indexes or relationships of the MS Access database using ADO.NET? Or do
I still need to use DAO to perform these operations?

As a side note, with a few tests that I've done already with some
queries, it seems like DAO is much faster than ADO.NET at running
queries and returning results. I am using the DataReader...does anyone
have any information about the differences etc? Thanks in advance...

 

[William \(Bill\) Vaughn]

This is an oft-discussed topic so be sure to use Google groups to search the
archives. Remember that DAO is the JET DBMS (the data engine used by Access
by default) "native" data access interface. JET is an ISAM engine that gets
some of its performance by permitting access to the "TableDirect" IO scheme
that simply walks a developer-selected index--this approach is not usually
implemented in traditional relational DBMS engines. ADO.NET in contrast is
very different. The Framework exposes several .NET Data Providers that are
written specifically to work with the target DBMS. For example, if you're
using OLE DB (via the OleDb namespace) instead of DAO you've just added a
complex and bulky layer between you and the data. There are several
alternatives to JET today. Anyone converting from Access/VB6 should consider
another (more scalable, more secure engine). Yes, SQL Server is an
alternative but now that MS has released the Compact Edition, it can also
serve as a fast, light DBMS alternative that's fully encryptable. It
supports TableDirect as well as scrollable, updatable curors. Yes, the
Compact Edition is a single-user DBMS engine so if you're planning to create
a multiuser application, you'll need to look back to SQL Server Express or
other engines.

Again, DBMS choice is a big (fundamental) decision--that's why its covered
in detail in my new book.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
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)
-----------------------------------------------------------------------------------------------------------------------

 

[Garry]

I had your problem basically and am working at the moment to transfer
existing Access 97 databases, (with data), to SQL Server Everywhere, the
new/old database referred to by William (Bill) Vaughn. He suggested to me
that I use this new database for .NET 2005 development where the DataBase
requirements are similar to what I had in a VB6/Access97 desktop
application.

I am working on an app to migrate the Access 97 database to the SSEv
database and it is going relatively smoothly. Although, there are some
problems as I have defined in a post a little above yours.

I do not expect to edit relationships within the predefined database OR
re-define existing tables BUT, I have seen reference to code which can do
this.

If you try to find code samples - do yourself a favor, use Google as William
(Bill) Vaughn valiantly suggests. Microsoft help is, in many cases,
gibberish, which will not solve your query and will just make you angry and
frustrated. I wonder when Microsoft will catch on a construct a serious
library of code samples instead of the meager offerings available.

If any of the Google search results take you to forums which require that
you 'join', be careful. Some of these forums even want you to pay to join
without you knowing if a solution is actually available there.

Be careful about using Sponsored Links as these must have some financial
incentives involved otherwise they would not be there. It costs money to put
a Sponsored Link on a Google Search result page. Every click means that
money is earned by Google.

If you want to go down this route, use 'Reply to Sender' as well as 'Reply
to group' and I will send you my code which is a near completed application
in VB.NET. I do not want to post my email address here.

I need an exchange of info and opinions from another programmer who is
having similar, if not identical problems.

Garry

 

[William \(Bill\) Vaughn]

I also share your frustration with the Microsoft doc. You'll also find that
Intellisense does not help either. That's because it leads you to think that
specific types, methods, properties and events are exposed when they are
not--you won't find out until runtime. To deal with this issue, I wrote my
first EBook that walks through the entire namespace and documents how it
really works. It's written for developers that are somewhat familiar with
the SqlClient namespace and ADO.NET in general (and those that have read my
Hitchhiker's Guide to Visual Studio and SQL Server book. This book is
available at http://www.hitchhikerguides.net/EBooks/Titles/5582.aspx.

Incidentally, Microsoft chose to change the name from SQL Server Everywhere
Edition to SQL Server 2005 Compact Edition about three weeks ago. This
version of the SQLCe DBMS engine has been tuned to run in a Windows Forms
environment. It's a fully relational database that supports a subset of the
TSQL SQL syntax. It's easy to deploy (just copy 6 DLLs), is fully
encryptable (so it's safer for data you take to the field) and has twice the
capacity of JET (4GB). SQLCe is also designed to act as a Subscriber to a
SQL Server-hosted Publication. This can make it even easier to construct a
distributed architecture around SQLCe. MS is still doing work on the Visual
Studio and SQL Server Enterprise Manager development tools but the EBook
shows how to get around these limitations until the next SP or Orcas ships.

Early next year, I'll be publishing the next in my series of EBooks. This
time on SQLCe Replication. I'm waiting for MS to finish work on a new
replication scheme that promises to be easier to use than their current
approaches.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
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)
-----------------------------------------------------------------------------------------------------------------------

 

[Garry]

Bill,

Perhaps you have an answer to the post two lines up.

I have other problems concerning the actual updating of data from my
accumulating DataTable but the basics of defining the tables with attendant
columns in a new SSCE data base are still not completely solved. However, it
does build the SSCE database, just some of the definitions are not included.

Also, SSEv31VSTools-ENU.exe with the SQL data management utilities will
simply not install on my machine.

Garry

 

[William \(Bill\) Vaughn]

The SQLCe engine requires the 2.0 Framework. Do you have that installed?
What version of Visual Studio and SQL Server is installed? I built database
tables from scripts and wrote my own batch processor to run them. These are
included in the EBook examples.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
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)
-----------------------------------------------------------------------------------------------------------------------

 

[Garry]

Framework 2

SQLCE 3.1 (Newest available - I think)

VS2005 Service pack Beta

Bought the book before I read your email.

The database is now copied with all the Data fields and values BUT, the
Primary key, AutoNumber is 'fudged' since in all my Access tables, the first
field, usually fldItemID, is the primary key and is an autonumber. In the
code, I know which is the first field.

The vb code does not manage to detect those specific properties.

field.Properties("ISAUTOINCREMENT").Value always equals False and so does
"KEYCOLUMN" even when I know that they are autonumber and PrimaryKey.

Any solution??

Garry

 

[William \(Bill\) Vaughn]

Frankly, (as I discuss in the book), I leaning away from Identity
(autoincrement) columns for a number of reasons. While these are supported
in SQLCe, there are situations where its SQL is too limited to permit proper
management and importing data with pre-assigned Identity columns. I think
that GUIDs (UniqueIdentifiers) make more sense for many of these databases.
This approach also works better when replicating the database (which does
not support Identity columns).

As far as getting VB to recognize the identity property of an Access/JET
database, I'm at a loss--sorry.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
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)
-----------------------------------------------------------------------------------------------------------------------

 

[Garry]

EBook is interesting and certainly fills in a lot of 'hidden info' on the
SSCE BUT, the zip file of the code in the ebook iz missing.

HowDoIGetIt????

The GUID seems such a HEAVY THINGY for lowly auto-increment need. It doesnt
tell me anything about in what order the records were originally created and
is very taxing to verbalise its content to someone over the phone. My usual
records/DataRows have an auto-increment for the first column AND, possibly,
a unique identifier that I generate per database and possibly, per table.

I turn your attention to a similar thingy with VB.NET where INI files are
not supported directly because Microsoft wants us to use XML format files
for this info. WHY make things more difficult. In VB6, they wanted us to use
the registry instead of INI files and now they want us, developers, to use
XML formatted files.

Remember the word KISS.

The book was a pleasure to read after the gibberish that Microsoft 'puts
out'. However, SQL Server Management Studio is nowhere to be found on my
machines and the msi will not install, dissappearing without a trace at the
end of 'configuring' files. No message. Nothing. I feel that I am not alone
in this and your book's content, in a large part, is based on this.

My basic inclinations are always to use code anyway. Something I learned
from trying to use the Data Tools in the first edition of VB6 7 odd years
ago.

My migration app works building a SSCE database file with imported data from
scratch in code using a source MDB. But, only the tables and some data types
which I do not use, may not be supported

Garry

 

[William \(Bill\) Vaughn]

I'm checking with Peter about the zip file distribution. It was supposed to
go out with the book.
See >>> below


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
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)
-----------------------------------------------------------------------------------------------------------------------

EBook is interesting and certainly fills in a lot of 'hidden info' on the
SSCE BUT, the zip file of the code in the ebook iz missing.

HowDoIGetIt????

The GUID seems such a HEAVY THINGY for lowly auto-increment need. It
doesnt tell me anything about in what order the records were originally
created and is very taxing to verbalise its content to someone over the
phone. My usual records/DataRows have an auto-increment for the first
column AND, possibly, a unique identifier that I generate per database and
possibly, per table.

I turn your attention to a similar thingy with VB.NET where INI files are
not supported directly because Microsoft wants us to use XML format files
for this info. WHY make things more difficult. In VB6, they wanted us to
use the registry instead of INI files and now they want us, developers, to
use XML formatted files.

Remember the word KISS.

The book was a pleasure to read after the gibberish that Microsoft 'puts
out'. However, SQL Server Management Studio is nowhere to be found on my
machines and the msi will not install, dissappearing without a trace at
the end of 'configuring' files. No message. Nothing. I feel that I am not
alone in this and your book's content, in a large part, is based on this.

 

[Cor Ligthert [MVP]]

Garry,

Your why's are probably because that in DOA you are handling recordsets.
A set of records where the identifier is in a connected state with the
Server.

In dotNet you are handling a dataset. Which has tables with rows in a
disconnected state.
For that it is much harder to handle the incremental identifier, there can
be (not wise) thousands of rows be supported to the server, while another
client was doing this as well. If your last record in your set is the last
added is even in this situation not for sure. (Although in SQL server it is
returned in your dataset, which makes than again a merge impossible).

Why this is changed, we are not thinking anymore about maximum 100 connected
users, we think that there can be possible 10000 of users who are using the
database but although they have the data, they are not connected at the same
time.

Just trying to give you an impression from your why's, while Bill will
obvious not go in discussion. I am Dutch we never have problems with that.



Cor

 

[Garry]

Thanks for the explanation Cor.

I really appreciate you explaining that and it is perfectly logical. I have
changed my mind bekoz I see the error of my ways.

It is true that I want to make my new NET implementation upward scaleble to
full SQL Server and I see the logic you explained in a multi user
environment with the disconnected datatables.

I will mull this thru because there is still a possibility that I will not
allow two users to access the same database file Read/Write at any given
moment. It is just not feasable to do so bearing in mind that a serious mix
up can result.

I need to improve my knowledge base.

Never too late to learn.

Garry

 

[Paul Clement]

On 17 Nov 2006 12:41:10 -0800, (e-mail address removed) wrote:

¤ I have an VB6 program that I wrote that access Microsoft Access
¤ databases using DAO. DAO has methods and properties that are Access
¤ specific (such as viewing and editing relationships or indexes on the
¤ tables) I want to write a new version of the program in VB 2005, and I
¤ want to use ADO.NET to access the data. Are there ways of changing the
¤ indexes or relationships of the MS Access database using ADO.NET? Or do
¤ I still need to use DAO to perform these operations?
¤
¤ As a side note, with a few tests that I've done already with some
¤ queries, it seems like DAO is much faster than ADO.NET at running
¤ queries and returning results. I am using the DataReader...does anyone
¤ have any information about the differences etc? Thanks in advance...

Some, if not all, of what you mention can be accomplished with Access SQL DDL, while others will
require DAO or ADO (ADOX):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

Yes, DAO will typically be faster. When using ADO or ADO.NET you're operating through an additional
layer of software, namely Jet OLEDB.

However, keep in mind that DAO is not supported by the .NET data access objects.


Paul
~~~~
Microsoft MVP (Visual Basic)