Access as a back end

[philfer]

Hello,

I have been reading about access development and how access cannot act as a true back end.

Is there no way to run the queries on an access db on a file server then use a separate access db to link to these queries only thus avoiding sending all the data over the network but just the query results?!?!?!

Cheers
Phil

 

[DCrake]

The major difference between Access and SQL is as follows:

If you rang up Access Motors and said "How many blue cars do you have in stock?" Access Motors would say "Just a second." They would then drive every car they had in stock to your house or workplace, then proceed to count the blue ones. When completed they would tell you, then drive them all back to their main dealership.

However, if you phoned up SQL Motors and asked the same question. They would do the counting at the dealership then tell you how many they have.

That is the simplest analogy between Access and SQL. Access can act as a true back end but it all depends on the config/setup of your network LAN/WAN

David

 

[philfer]

Thanks for that, it was very helpful and a very clear analogy.

Is there anywhere I can find resources about setting up Access as a true back end??

Thanks again
Phil

 

[Rabbie]

Thanks for that, it was very helpful and a very clear analogy.

Is there anywhere I can find resources about setting up Access as a true back end??

Thanks again
Phil

I have been reading about access development and how access cannot act as a true back end.

You got it right the first time. Unfortunately Access doesn't work the way you would like it to. There is a free version of SQL Server which will do what you want so I would google for details.

 

[Banana]

When you link a table from other source, be it SQL Server, MySQL, Oracle, DB/2, Access/Jet will behave differently and can act more like the SQL Motors in DCrake's analogy.

To make sure this happens, I would say that two papers are a mandatory reading: Optimizing Client/Server Performance on Access, and Whitepaper on Jet/ODBC Connectivity. Bolth can be found at support.microsoft.com and while dated, they are still valid for all versions.

 

[Pat Hartman]

You can jury-rig something but I'm not sure it would be worth it.
1. You would need to have the back-end reside on a Windows based server - UNIX won't work.
2. Access would need to be installed on the server. You may have trouble getting this past the network admin.
3. The "back end" database must be open and a form with a timer event needs to be open.
4. The form with the timer event will be continually look for query requests that are posted to a table.
5. The front end would need to place the query request in the linked table that the back-end reads every time the timer event fires.
6. When the back end finds a pending request, it needs to execute it and export a .csv file with the query results.
7. The front-end needs to loop, looking for the appearance of the .csv file. When it appears, the front-end reads it and does whatever you want it to do.

If it sounds like a lot of work, you're right. Have at it.