How to increase storage space in MS access

Hi

Is there a better way to deal the situation below:

(1) I have a back- end with 64 tables and currently it is occupying a total of 2.75 MB, now I want to split it to increase the total spare size to 8 GB, I know some might say the SQL is the answer, from experience it is not due to the following challenges:

(1) In most cases the DELETE action query fail to delete data in SQL back-end via ODBC connection, while others work like insert or update queries
(2) Some sub-forms with formula fail also to calculate especial the calculated controls.
(3) I have not seen any issue relating to my VBA code except the above two!

So for now I would not want to continue experiment this SQL Server thing against MS Access because there could be some other hidden issues not revealed, even using C# sharp it is a lot of challenges to make it work!

The main issues are here!

(1) The payroll shares the employee details with the sales accounting, if I move all payroll related tables into a new database can the sales accounting work???????
(2) The Point of sales shares the products & warehouse with the sales accounting & inventory, now if I move the Point of sales tables to a new database won't it affect the others??????
(3) The Fixed register share the accounts codes to all can this be moved without problems??

Currently my FE has 13.5 MB and BE is at 2.75 MB

Regards

Chris

Fabrice Lambert

Hi,

(1) I have a back- end with 64 tables and currently it is occupying a total of 2.75 MB, now I want to split it to increase the total spare size to 8 GB,

MS access manage the database size automatically, you can't allocate a pre-defined size.
Also, beware that an access database can't exceed 2GB, else it will get corrupted.
So, if you're data will need over 2GB, look for another solution.

(1) In most cases the DELETE action query fail to delete data in SQL back-end via ODBC connection, while others work like insert or update queries
(2) Some sub-forms with formula fail also to calculate especial the calculated controls.
(3) I have not seen any issue relating to my VBA code except the above two!

Please, provide more détails, such as:
Database / forms design.
Validation rules.
Data macro.
VBA.
(possibly upload your database with anonymous data)

As it stand, there isn't much we can do.

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

(1) In most cases the DELETE action query fail to delete data in SQL back-end via ODBC connection,

Just try the delete query by simply changing the select query to delete in MS access, then use it to delete the data rows in sql server confirm here whether that query will work? The delete query works very in a native MS Access but the same does nothing in SQL Server. Or paste the syntax as well. I tried this by running the query manual and by VBA code , the message says " Zero number of rows is deleted"

Regards

Chris

Ryan Chong

(1) In most cases the DELETE action query fail to delete data in SQL back-end via ODBC connection

This shoudn't be the case. Have you checked your ODBC credential and make sure it got enough privilege to delete records? It also depends on "how you delete" such records in your Access (VBA). Otherwise, you may consider to build a DB layer (like a Stored Procedure) in MS SQL for the record deletion task.

Fabrice Lambert

To my knowledge, delete queries have a slight syntax difference between Ms Access and SQL server.

Sample queries:

-- MS Access
DELETE *
FROM MyTable;

-- SQL Server (notice there is no "*")
DELETE
FROM MyTable;

Open in new window

Also, some function calls have a different syntax and/or have a different behavior.

But again, without details ....

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial