Methods of accessing Access database data.

[Kraeg]

In previous databases, I have done it all in Access; the backend and the frontend.

Recently, I have begun transferring (more out of interest at first, but now with the plan to replace the originals) it all to a VB5 frontend that accesses the backend MDB.

I am avoiding Data Controls, and am using recordsets, SQL statements, etc. to load records. These I load into variable arrays, and going from record to record (using Next, Previous buttons, etc) shows the contents of the appropriate one in the appropriate Text Box etc. When saving, it changes the contents of the corresponding variables, opens a recordset, puts those variable values into it and updates it (so it updates in the backend MDB).

Is this an acceptable method? Are there any dangers, concerns I may be missing? It works okay here at home; on a standalone PC. But how would it run on a network with up to 15 clients accessing it?

Also, any relationships I put in using code; I haven't set up any in the MDB. I find I prefer doing this. But then again, I may be missing something.

 

[johnwm]

Watch out for inherent Access limitations - it's not really designed as a database server engine. I've found that it will really struggle with more than 2 or 3 concurrent users, depending on activity of course.

I would write your first version using MSDE, which, although not MUCH better than Access, is at least written as a database server. It works well up to 5 concurrent users and up to 2 GB database size.

It slows down with more than 5 or so concurrent users, but it's code compliant with MSSQL, so you can very easily upgrade as and when required. Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[sunaj]

A thread not that long ago (summer maybe, I can't find it agian) showed that most of the 'top experts' that you see in the list to the left, do not like/use data binding. E.i. you are on the right track.
However it is not custum to copy the recordset contents into an array. Why not keep the data in a recordset (make it e.g. 'static/ if you want to move back and forth in it).
For a small number of updates you can use the .execute method of the connection object to INSERT/UPDATE data, for a large number of inserts in the same table is pays off to open a recordset (adCmdTable), add all the data and then .update.

Good luck Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Kraeg]

For now, I'm sticking with Access as the frontend..... weighing all the pros and cons (at this time), it works out better. But I will eventually move into my own interfaces in VB. Thanks.

 

[JonJacobik]

Here are the commands you will need:

set db=opendatabase("YourDB.MDB&quot
set opt=opentable("access tablename&quot

additional tables: set xyz=opentable("access tablename&quot

each field is addressed as opt("Fieldname&quotor xyz("fieldnametoo&quot

You'll also need these commands:
xyz.addnew, xyz.edit, xyz.update

I use arrays and other program variables to read the records, then update the access file before I exit. I also prefer to create the database within VB6 using createdatabase command.

 

[CasperTFG]

I's stay away from ODBC and DAO... They cause many memory and logging issues. For instance using ODBC to access an SQL server can create temp files 4 times the size of the actual database.

You might as well start off where you should end up, with using ADO. Don't bother with keeping information in arrays when keeping it in recordsets is so much more powerful. ADODB Recordsets give you all the tools you would need, like MoveFirst, etc. and advanced featers like Filter and Sort.

If your looking at having 10 concurrent clients, then use disconnected recordsets. i.e. You get the records you need and then
<< moment of silence for rememberence day >>

Okay so you get the records from the DB, then disconnect the DB, do what you need to do and then everytime you want to save a record re-connect issue commands and disconnect.

Here is some sample connection Code.
[tt]
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDB.MDB&quot;
cn.Open

strSQL = &quot;SELECT * FROM MyTable;&quot;
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
cn.close
[/tt]
That gets your records and then disconnects the DB. That way other users aren't affected. Craig, mailto:sander@cogeco.ca

Si hoc legere scis, nimis eruditionis habes