Access AS SQL Front End
-
We have a DB that was migrated from MS Access to SQL Server 2008 R2. I wanted to continue to use Access as a front end to the DB since users are comfortable with it. But Access is unable to open the database without crashing frequently, corrupting tables, etc. etc. There are tables that are linked to other tables (iCommodity is an int key to Commodity.ID etc.) The largest table is 250,000 records ~ 100MB. It appears that most of the issues revolve around extended properties. Erasing them gets Access to work temporarily but it usually crashes again after re-writing the properties. Is there any known issue that causes this behavior? What do other people do? I can write a application to get into the data but it seems ridiculous that Access can't even open a table without crashing. Thx Mark Jackson
-
We have a DB that was migrated from MS Access to SQL Server 2008 R2. I wanted to continue to use Access as a front end to the DB since users are comfortable with it. But Access is unable to open the database without crashing frequently, corrupting tables, etc. etc. There are tables that are linked to other tables (iCommodity is an int key to Commodity.ID etc.) The largest table is 250,000 records ~ 100MB. It appears that most of the issues revolve around extended properties. Erasing them gets Access to work temporarily but it usually crashes again after re-writing the properties. Is there any known issue that causes this behavior? What do other people do? I can write a application to get into the data but it seems ridiculous that Access can't even open a table without crashing. Thx Mark Jackson
mjackson11 wrote:
Is there any known issue that causes this behavior?
Yup. Access tries to get everything into memory. But why stick to Access? You could migrate away from a bloated client to a cleaner UX using just about any other language. I think VB3+ODBC gave better control then Access [as a UI] ever did.
Panic, Chaos, Destruction. My work here is done. or "Drink. Get drunk. Fall over." - P O'H OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre Have a bit more patience with newbies. Of course some of them act dumb -- they're often *students*, for heaven's sake. -- (Terry Pratchett, alt.fan.pratchett)
-
We have a DB that was migrated from MS Access to SQL Server 2008 R2. I wanted to continue to use Access as a front end to the DB since users are comfortable with it. But Access is unable to open the database without crashing frequently, corrupting tables, etc. etc. There are tables that are linked to other tables (iCommodity is an int key to Commodity.ID etc.) The largest table is 250,000 records ~ 100MB. It appears that most of the issues revolve around extended properties. Erasing them gets Access to work temporarily but it usually crashes again after re-writing the properties. Is there any known issue that causes this behavior? What do other people do? I can write a application to get into the data but it seems ridiculous that Access can't even open a table without crashing. Thx Mark Jackson
Can you post a sample of the code which is causing Access to crash ? Are you accessing the SQL tables via Access linked tables ? Are you sure that you are fetching only the data you need and not entire tables ? Maybe I can offer some options that can get you over these pain-points without an entire application re-write.
-
Can you post a sample of the code which is causing Access to crash ? Are you accessing the SQL tables via Access linked tables ? Are you sure that you are fetching only the data you need and not entire tables ? Maybe I can offer some options that can get you over these pain-points without an entire application re-write.
Oh so now YOU want him to
snd codz plz:laugh:Never underestimate the power of human stupidity RAH
-
We have a DB that was migrated from MS Access to SQL Server 2008 R2. I wanted to continue to use Access as a front end to the DB since users are comfortable with it. But Access is unable to open the database without crashing frequently, corrupting tables, etc. etc. There are tables that are linked to other tables (iCommodity is an int key to Commodity.ID etc.) The largest table is 250,000 records ~ 100MB. It appears that most of the issues revolve around extended properties. Erasing them gets Access to work temporarily but it usually crashes again after re-writing the properties. Is there any known issue that causes this behavior? What do other people do? I can write a application to get into the data but it seems ridiculous that Access can't even open a table without crashing. Thx Mark Jackson
mjackson11 wrote:
What do other people do?
Typically no one with any choice would use Access as a front end, you are going to have to upset your users, unless that is you want to write the UI to match the look and feel of Access. I can think of worse fates for a developer but not many.
Never underestimate the power of human stupidity RAH
-
Can you post a sample of the code which is causing Access to crash ? Are you accessing the SQL tables via Access linked tables ? Are you sure that you are fetching only the data you need and not entire tables ? Maybe I can offer some options that can get you over these pain-points without an entire application re-write.
There is no source code as the tables are linked directly to the SQL server. Access crashes if you try to open the table. It works for smaller tables but once you get past 50-60 MB it gets very dicey about crashing. I suspect it is pulling entire tables.
-
We have a DB that was migrated from MS Access to SQL Server 2008 R2. I wanted to continue to use Access as a front end to the DB since users are comfortable with it. But Access is unable to open the database without crashing frequently, corrupting tables, etc. etc. There are tables that are linked to other tables (iCommodity is an int key to Commodity.ID etc.) The largest table is 250,000 records ~ 100MB. It appears that most of the issues revolve around extended properties. Erasing them gets Access to work temporarily but it usually crashes again after re-writing the properties. Is there any known issue that causes this behavior? What do other people do? I can write a application to get into the data but it seems ridiculous that Access can't even open a table without crashing. Thx Mark Jackson