Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
  1. #16
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862

    Quote Originally Posted by wcrimi View Post
    Is it easy to convert all my data and queries to SQL Server Express?
    Unless I am missing something, it seems you are copying and duplicating records, archiving one year to the next. I say it would be easier and more effective, in the long run, to normalize your data structure and not duplicate data.

  2. #17
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Quote Originally Posted by ItsMe View Post
    Unless I am missing something, it seems you are copying and duplicating records, archiving one year to the next. I say it would be easier and more effective, in the long run, to normalize your data structure and not duplicate data.
    There is some of that going on because certain queries were massively easier to write if I first extracted the records I needed into a holding table and then ran a bunch of queries against that extract. Then I empty that holding table. The problem I am having now is that I can no longer build that holding table.

    I just created a 2nd database with just the monster table and nothing else. It takes up 1.384 Gig. It's a whopper and it will continue growing. So eventually it will get back up to 2 gig. If I do what you suggested and link that new database to the current one (after deleting that same table in the current database and not before doing some reading hahaha), that should buy me about another 6-12 months to come up with a more permanent solution.

    I need to learn how to link databases so the queries in my current database can still use that table even though it will be located in another database.

    Does it sound like I am on the right track?

  3. #18
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Yeah, make a copy of your DB and delete all of the queries, forms, modules, etc.

    Place that copy in a share folder where the clients will have full privileges, execute, read, etc. Do a compact and repair of that.

    Make another copy of the original DB and place that on your local machine's C drive. Delete all of the Tables. Then, run the Wizard (Linked Table Manager) or maybe External Data for the first time. The idea is to link to an Access accdb. Then do a compact and repair.

    That is basically it. Afterwards, consider bringing those tables that are holding temp data into the Front End (FE) file. These are commonly referred to as Temp Tables. You do not delete temp tables. The are simply a structure to import snapshots for your client to work on. That might be a start.

    I do not know what your financial situation is. It might be worthwhile to dedicate a machine to Access and install 64 bit Access. The default install is 32 bit. 64 bit allows for larger files sizes.

    Ultimately, if you DB is growing exponentially at the rate described, you need to normalize the data.

  4. #19
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Quote Originally Posted by ItsMe View Post
    Yeah, make a copy of your DB and delete all of the queries, forms, modules, etc.

    Place that copy in a share folder where the clients will have full privileges, execute, read, etc. Do a compact and repair of that.

    Make another copy of the original DB and place that on your local machine's C drive. Delete all of the Tables. Then, run the Wizard (Linked Table Manager) or maybe External Data for the first time. The idea is to link to an Access accdb. Then do a compact and repair.

    That is basically it. Afterwards, consider bringing those tables that are holding temp data into the Front End (FE) file. These are commonly referred to as Temp Tables. You do not delete temp tables. The are simply a structure to import snapshots for your client to work on. That might be a start.

    I do not know what your financial situation is. It might be worthwhile to dedicate a machine to Access and install 64 bit Access. The default install is 32 bit. 64 bit allows for larger files sizes.

    Ultimately, if you DB is growing exponentially at the rate described, you need to normalize the data.
    I split the database in 2, created the linkage, and everything is working fine so far. Thanks for the help.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Microsoft Jet database engine ERROR message.
    By jparker1954 in forum Access
    Replies: 6
    Last Post: 06-11-2015, 08:17 AM
  2. Error Message when opening Database
    By lowesthertz in forum Security
    Replies: 5
    Last Post: 03-28-2015, 07:01 AM
  3. Replies: 2
    Last Post: 03-28-2014, 07:30 AM
  4. Custom error message problem
    By thekruser in forum Programming
    Replies: 10
    Last Post: 10-06-2010, 05:14 PM
  5. error message when opening the database
    By dollygg in forum Access
    Replies: 1
    Last Post: 10-04-2010, 08:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums