Can VB's DBEngine open 2 databases using 2 different MDW files? 2

[ibanezGuitars]

Hello,

I am revamping a VB6 system using Microsost's DAO 3.6 Object Model and Access 2002 databases on a Win XP machine. There is a Master.mdb database on the server that everyone shares and there is a Local.mdb database on each user's local hard drive for their own individual needs.

Can I use 2 separate .mdw (security) files to open each database individually?

In other words, as users logon to the system, can I have all of the users open the Master.mdb file on the server using the same Master.mdw security file (also on the server) and then have each user open their individual Local.mdb files which reside on their local hard drive using their own copy of the Local.mdw file (also residing on each user's hard drive) ???

Any suggestions or code snippets would be greatly appreciated.

Thanks for your time,

Dave

 

[simonkue]

If you use 2 connection objects the logon's to each database will be handled by each connection. I have written various routines which talk to tables on two databases which can be on two separate pc's.
I use a config file to give me the database locations and read this in at startup. That way, I can move the databases without recoding the application.
I have had problems combining tables from both db's in SQL - particularly in joins so beware there. Simple SQL should let you talk to tables on different databases however.

 

[JerryKlmns]

ibanezGuitars ,

Could you link the Master.mdb tables to each local.mdb so you can open one connection or one DBEngine and use all tables to join easily?

 

[ibanezGuitars]

That is actually how it was initially set up but now, the number of concurrent users has grown to about 45 on at a time. I'm not sure but I suspect that the record locking on this one mdw file may be what's dragging the entire system down.

Dave

 

[thendrickson]

I have had the misfortune to have worked with a large number of concurrent users using Access in the past. Usually because a sound design grew out of control over time

I would guess that performance problems are not likely related to the mdw file

Without being able to study the problem thoroughly, I do have several suggestions.

Consider moving to SQL Server. There are so many advantages to doing so that I truly believe Access should be used only for very small operations.

A single processor license is not very expensive either.

You can also link the access tables to SQL and live with the poor performance of linked tables untill all your front ends have been updated.

That being said, First take a look at your queries.

I will bet you are returning way more data than is being used.

Are the end users writing their own queries perhaps?

I would put money they do a select * when they only really need a few columns.

Things like that.

Good luck.

 

[ibanezGuitars]

Thanks for the reply. The database is secured and there are no queries or reports - just the tables. The querying is done from VB but they're a wee bit bloated:

"Select * From Clients Where LName = 'Smith'"

I'm guessing there are well over a 100 fields in this table alone so this is an area I will be modifying.

There are also a lot of tables containing "Lists" of items like CaseStatus which can be (AP =Approved, DN = Denied, and so on. These tables populate the pull-down boxes on the forms the users are keying into.

Although these "list tables" are not involved in any hard-wired relationships to other tables, do they get momentarily locked during the .Edit and .Update of the Client record using Optimistic record locking??

If I were to move these tables containing these "list fields" onto the local database on each user's PC and populate the pull down boxes from these new table locations - would it provide any benefit in terms of performance or reducing error locking errors?

Thanks again for your time,

Dave

 

[thendrickson]

Performance problems can be very difficult to isolate.

Their is seldom only a single culprit.

You need to look at the total package.

I do not care for embedded SQL even for Access since it makes things more difficult to track. I prefer SQL Server and stored procedures.

That being said, I do have some constructive advice.

1. Look at your indexes. Be certain you are indexing the fields you are searching on.

2. look at your cursor types. Choose the lease expensive for what you actually need. You do not beed a dynanic cursor for reports
or filling a combo box. Study help on the cursor types available to you

3. How are you calling the SQL statements?
Look at help and try to understand which options will work best for you.

4. How about connections? Can you use disconnected recordsets? Also are you opeing a connection for each user and leaving it open? sometimes you want to do that, but normally I would say not to.

5. Speaking of connections, ADO is best for most use and use the donnection string. I do not often use an ODBC connection.
Hint: You can 'cheat' a bit and use the data control to help create the connection string, then use that in your code.
You can also use a direct connection for DAO if your code limits you to that. Look at changing to ADO and doing
insert and updates directly

6. Bound controls? Not for a big app to my way of thinking..

7. Explicitly close recordsets and connections. Then set them to nothing.

8. Look for Access functions within your SQL. If (unlikely) you are using anthing like DCount etc, change that now!!!!

You are on the right track about the select *. But check your where clauses as well.

I suspect if you fix your indexes, fix the SQL statements returning more columns than use and improve your selection criteria, you will improve things dramatically

Some of this depends a bit on how much time and authority you have of course. I would scrap DAO at once and go to ADO but sometimes you can be blocked.

And, yes you could be locking those list tables but not likely unless you are using dynaset cursors or something.

I do not recall DAO well so you need to look up which cursor to use, but use a read only forward only cursor for filling read only controls

Good luck

 

[thendrickson]

Almost forgot.

How often do you run repair and compact against the Access databses? Access is horrible on how it stores data. The repair and compact helps a lot.

Also check your data types in the tables.

Access uses all the space you allocate even if it does not need to.

For example if you have a character data type with a length of 255 when you need 5, you are just adding to your problems.

And yes, I have seen every field in a table set for a length of 255, even for the state field!!!

 

[djj55]

FYI Access will import a table and automatically set the length of character fields to 255.

 

[thendrickson]

djj55

I do not believe you are correct.

It has been a long time since I worked with ACCESS. The last several years,when I did work with Access I have only imported Access into SQL Server or did nothing with import/export. However, until 2004 I did a lot with Access and I do not recall any troubles with column sizes after import or export of Access tables into other Access db's

Also, I did a quick import of a single table into a new Access database from an Informix database and the field sizes imported more or less properly The imported data matched the design size in the Informix table for character data but screwed up the Informix bit data type.

Not a fair test of course, but the best I can do right now

I do not have an ACCESS database on this laptop so I will need to check this out tonight if I have the time.

 

[djj55]

thendrickson
Just imported from the strange and unusual Excel - no wait both are Microsoft. The text fields came in as 255 while they only needed to be 20.

I have also had trouble with other imports but sometimes MS does it well.
djj

 

[JerryKlmns]

djj55,

On menu Tools-->Options Tables/Queries tab, the Default field size for Text could range from 1 to 255. Make your selection there and new text fields in any table inherit this amount of space. No matter what your field size is for a text field the size on disc is the size used. The last has been tested here in Tek-Tips, (don't have that post handy).-

 

[thendrickson]

apologies

I made the mistake of assuming that a "table" meant a database table, not a spread sheet.

Also curious about the field size issue but I will reserve comment until I can research that further.

Regardless, I stand by my postings.

There are many factors involved in performance problems.
Seldom will there be a "silver bullet" to correct performance problems.