Best practices / approach for Windows apps using Access?

  • Thread starter Thread starter Mark Findlay
  • Start date Start date
M

Mark Findlay

Hello experts,

We are normally used to coding to SQL Server, but have a requirement to
write a Win32 app that will store upwards of several hundred thousand rows
in an Access database per run. (Initial run could be hundreds of thousands
of records, subsequent runs would update/add/delete columns and rows). This
app would make use of SQL TRANSACTIONS to be able to commit or rollback
entire runs.

We expect that we would need to use an ODBC Jet engine for I/O in order to
be the most compatible with the most client machines.

Can you offer any advise, warnings or other potential issues we should take
special care to avoid or code to?

thanks!
Mark
 
yeah if you know SQL Server; then why in the hell would you use a
junior database like Access?

Access MDB is not a database; spit on your boss and then quit
 
SQL Server 2005; it is quite easy to package portable SQL databases.

So Access has _ZERO_ benefit from a portability standpoint


Seriously-- what would you have to gain from usnig MS Access?
 
ODBC Jet?

ODBC is OBSOLETE
JET is OBSOLETE


do you know anything about databases?


seriously

Access MDB is for ****ing babies
If you like Access Frontend, then you should use Access Data Projects
against SQL Server
 
I'm not sure exactly what you plan?

How are you writing the Application? In C++? In Access?
Why are you using ODBC? To talk to Jet/MDB? To talk to SS?

ODBC is rarely the best way to talk to Jet.

ODBC/Jet SQL has no SQL support for Transactions.

Do you mean SQL Transactions or SQL Server Transactions
or ADO Transactions or DAO transactions?

DAO Transactions using the current DAO version can't be
used for ODBC Transactions anymore (broken).
Mixed mode transactions, using MDB tables and SQL Server
tables, don't work anymore except in the simplest cases (broken)

(david)
 
I'm not sure exactly what you plan?

How are you writing the Application? In C++? In Access?
Why are you using ODBC? To talk to Jet/MDB? To talk to SS?

ODBC is rarely the best way to talk to Jet.

ODBC/Jet SQL has no SQL support for Transactions.

Do you mean SQL Transactions or SQL Server Transactions
or ADO Transactions or DAO transactions?

DAO Transactions using the current DAO version can't be
used for ODBC Transactions anymore (broken).
Mixed mode transactions, using MDB tables and SQL Server
tables, don't work anymore except in the simplest cases (broken)

(david)
Click to expand...

Also, although Access may be able to handle this kind of volume if you
design it well, are you sure you don't want to use SQL Server 2005
Express? It can be installed FREE on each client PC.

It's not quite as easy of an install as a simple MDB, but it's much
more powerful.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back
Top