Converting ODBC Tables to Access Tables

[MurrayP]

Hello, this is my first post (anywhere ever)!

While I have lots of Access programming experience over the years I have broken new ground this week and am in unchartered territory and need help please!

In the organization I am working for (as Procurement Manger), we use AMMS (by Microwest) for work order generation and inventory management, etc. I have been able to import into Access the tables of interest from this SQL-based package. The problem is that I need to share the tables (via nice front end forms/queries) with about 20-30 people in the field.

I don't know much about ODBC but the administrator of AMMS was kind enough to give me access to the AMMS database tables via ODBC and I, personally have access to read the table data but none of the other people in the field can access these tables due to security rights.

So, what I have done is I created MS Access tables for each of the 4 AMMS tables of interest and I run code that runs delete and append queries on each table. I then have a separate public db that the field people access with all the queries and forms but only links to the Master db containing the 4 Access tables derived from the ODBC tables. This works ok but there are issues:

1) if any field people are on the Public db, the code to import the ODBC table data loops and does not complete the delete/append cycle on one or more of the tables. I can only refresh the table data when nobody is connected to the db - impractical.

2) the ODBC tables are rather large - one is over 38,000 records and it takes several minutes to run the delete/append queries.

3) When I created the Master db it was some 83MB. After running the delete/append queries it is some 179MB and appears to increase in size every time I run the delete/append code. When I run "compact db" it stops running after a short time. Does nothing but create a new file (db1.mdb) of the same huge size as the Master.

I suspect the most graceful solution would be to give each user rights to the ODBC tables from AMMS in Access. In this case, I wouldn't have to run delete/append queries and the data would always be in relative real time subject to the refresh timer. Unfortunately, due to AMMS licensing restrictions, this is not doable.

I have tried to search the internet for guidance but no luck. This forum appears to be an excellent opportunity to get expert advice on this particular issue. I thank you for your thoughts and guidance.

 

[llkhoutx]

(1) Advise the user via programmatic message they they must exit the program for maintenance. If they don't get off after a couple escallated and timed messages, kill their job.

(2) Welcome to database world. Certain programming practices, too numerous to spefify here, can cause queries to run long.

(3) You are experiencing "program bloat." I would put tables that have rows repreatedly deleted and appended in one or more separate databases, linked to the instant program.

 

[MurrayP]

Good Ideas

Thanks for that. In the past several days since I posted, I have experimented more and indeed separated my database master into 2 separate files with the slaves connect to 2 master files instead of one. Made a huge difference in file size - down from over 200MB (compact/repair db command kept failing) to approx 20MB for the main file that holds the 4 ODBC tables and converts them to Access tables through the code that runs delete/append on each table.

A main contributor is that I pared down the Access tables to hold only the several fields from each ODBC table my program needs instead of a complete append of each ODBC table, one of which is over 38,000 records - a fair size for Access I would suspect (particularly with over 25 potential users of this tool via Access Viewer).

A new issue has developed and your guidance would be greatly appreciated. The program that holds the tables I access via ODBC has a licensing constraint of 19 seats. When the 20th user goes to log on to the host program (AMMS), the program will deny access. I have heard from one person that when I access the ODBC files via Access, I am "probably" using 1 of these licenses. Another fellow who administers the AMMS database for my employer didn't think that this was true. If I am not occupying one of the licenses, then I would like to make each of the 4 ODBC tables available to each of the 25 users and skip the delete/append non-realtime approach. So, what is the truth about this particular issue? It would be great to give all these users realtime read access to the material status from the AMMS program via my simple to use Access front end.

Thanks again for your learned thoughts!

PS - regarding your point 1), I don't have the knowhow to code what you are suggesting. Would you have such a routine to offer?