Advanced MS Access Programming Q?

[UThomas]

I'm an access developer right now and had a few q's:

-Transactions: When programming in VBA how do these work? Is the workspace object created in a temporary location on your local computer? I've seen a lot of benfit from adding this in to one of my main functions. It edits about 1000 lines in a table and the addition of transaction commands has reduced network traffic by a factor of 10 (which results in a lot less of those annoying "Disk or Network Error" errors).

-ADO vs DAO: I'm fiddling with using SQL Server 2k as a backend with Access 2k as the front end. Is it just me or when you link tables into SQL Server its quite a bit slower? Would this help if I went from the native DAO programming to ADO calls and recordsets?

Thanks!

Thomas

 

[bunker]

I'm not an advanced Access programmer, but I'll try to help you out.

1. I'm not sure how they work in vb, but I do know you need to be careful using them if your data needs to be kept up in real time.

2. If you're going to use sql2k then don't create an .mdb and use linked tables. We just developed an app in Access using sql2k as the backend. Set up a new Access project (.adp) and use that. It connects directly to the sql database and there is no linking involved. Much quicker and easier to access views, stored procedures, etc...

fwiw, my experience is with sql and I'm learning the Access as I go.

 

[UThomas]

Thanks! I'm with you on the real time nature of the transactions. Right now I'm using them for bigger batch processes (automatting and formatting an excel template upload) and I was getting network errors before.

Can you tell me more about the methodology of an *.adp vs an *.mdb file? Are you programming in DAO or ADO (DAO is the default for MS Access)?

Can you recommend any books to read?

Thomas

 

[UThomas]

ttt

 

[EagleKeeper]

A little side note.

Did a system in VC with Access 97 as the database.

I went to a M$ seminar/trade show and brought up the question of ADO vs DAO.

Answer was the DAO was faster.

 

[OZEE]

Dunno how much data you're wanting to log, but Access is NOT the product of choice for logging very much industrial data. It's really not a very good database for these types of applications. The files get way toooooo big, too fast, then it starts slowing down, requiring regular database compaction and other housekeeping...

If you can go directly to the sql-based structure, it'll be lots better.

As an industrial controls system integrator, I've been through this problem more than once....