SQL backend general question

[dan231]

We have a SQL 2000 backend and a Access frontend, designed in-house, but it seems to me that the SQL might not actually be used at all?

How do I tell if it really is being used?

The Access FE has all the data tables, reports, etc and there is plenty of code in the forms. It seems to me that if the SQL was being used or being used properly, I would have less access tables and more code pointing to the SQL db.

Am I totally off base here?

 

[KenHigg]

This is kind of an odd question. A table is either in access or it isn't. The first acid test is what kind of icon it has in the database container. I think the SQL Server icon is a globe (world) looking thing. And a linked table be it an access table or other wise will have a small blue arrow on the left side. Knowing this, can you elaborate?

 

[Banana]

Well, it's not a black-and-white answer.

Normally, you do all validating, interface, and presentation within Access while backend is usually concerned with only actual tables, storing data, and perhaps passing a stored procedure or firing a trigger if desired.

So the right question should be more of: Is Access utilizing the data from SQL server as effectively as it can?

To which you can go to MSDN and search for Access's server/client performance and also the whitepaper on Jet & ODBC connectivity to help you know what issues you need to consider in the design and ensure that everything is running at peak efficiency.

 

[dan231]

yes, all my tables are blue worlds with the arrows. So does that mean they are in the SQL db and just linked to access?

I guess I would have thought there wouldn't be as much duplication. My "issue" is that we are on terminal services here and the Access FE is copied locally to each users profile, which adds about 75mb to each users profile.

Thanks for the info about the icons - that was new info for me

 

[dan231]

Thanks Banana, I will check that out as well!

 

[Banana]

Well, I'm not totally familiar with terminal services, but terminal services or no terminal services, each user does have to have their own copy of front-end to protect against corruption. From sound of it, it does sounds like a big and important database.

Furthermore, if you really are scarce on memory, I wonder if having a library database can help by consolidating common routines and functions to be shared among users...

 

[dan231]

...if having a library database...
ok, way beyond me now lol!

We are trying to get someone to take a look at it to either fix it or possibly rewrite the whole thing. - And yes, it houses all our time sheets, client info and just about everything else too. Long story, but it really wasn't ever finished and has lots of glitches.

 

[KenHigg]

Yes - Sounds like the tables are in a SQL Server back end then. If all (or even most) of your production data is in the back end then 75m sounds large for just a FE file. Have you compacted it?

 

[dan231]

Yes we have compacted it, but it goes back to the 75mb within a week or 2

 

[KenHigg]

What does it compact down to?

 

[dan231]

haven't done it in a while, but I think around 60mb

 

[Darth Vodka]

use a batch file to copy a master front end to each c:

then you never need to compact it or distribute it

 

[MagicMan]

If the applications are not coded correctly for SQL Server, Access pulls back the data and uses the JET engine to resolve data access statements that SQLSERVER cannot resolve. This can cause a lot of traffic between the workstations and the server, as well as causes the FE to grow in size. 60M sounds like you have more than just application data in the FE. The following site is a good reference on differences between Accces (Jet) and SQLSERVER. I no longer have the link that identifies specific coding examples that cause data to be pulled back to JET (Sorry).
http://sqlserver2000.databases.aspf...ifferences-between-access-and-sql-server.html

 

[dan231]

ok, thanks for the link. I'll take a look at that.