Access DB too large, ideas?

[anoble1]

I have a Access database that is over a gb. I have 2 tables that I dump monthly info in. When I open the table it takes over 5 minutes for it just to open the table.

Any suggestions? Don't have a SQL Server or money for one.

[twinnyfo]

anoble1,

What do you store in your tables (I know, data--but what kinds)? Is your DB fully normalized (this can help significantly on wasted resources. Also, have you compacted and repaired your DB recently? Be sure to make a back up first before you compact and repair.

[anoble1]

You may have to help me out a bit here. I compact and repair every few days for sure. Do you mean what data types I guess?

[Seth Schrock]

FYI you can get SQL Server Express for free.

[anoble1]

I don't know much about SQL Server. But, I do not have a dedicated PC for SQL Server. I have my PC. I am the only person who uses this though. Wonder if I could throw that on my pc and just store data on that and link it?

[twinnyfo]

My question was in terms of types of data. If you have attachments included in your database, your file could grow rather large. Keep in mind that I have no idea what your DB is used for, nor how your tables are built. But, from a "basic, generic MS Access DB user" standpoint, a DB over 1 GB in size is almost inconceivable to me.

A coworker of mine has a DB that is 600 MB, but it is a RAW DATA pull that contains hundreds of text fields and the entire thing is 100% un-normalized. I use a RAW Text DB pull that is about 300 MB, but after I extract the necessary data and normalize it for my purposes, it is about 150 (still a lot).

My point is, I'm just wondering what you are storing that contains 1 GB of data. not that there is anything wrong with what you have or how you are doing it. Just a little amazed at the size of the file for a low-budget operation.

[anoble1]

Attached is the data that I import into Access using the Wizard. No attachments.

[twinnyfo]

Only 15 fields? It looks like it is all text, too. It might be possible to normalize the data during the import process. I assume you receive the data in a spreadsheet? how many lines are imported each time?

our friend JForbes could probably calculate how many rows you would need to max out Access with only 15 Fields, but it must be a lot.

[anoble1]

Varies.. 15,000-50,000 records in a sheet. When I open the table it says running query at the bottom of access. Wonder if it would help if i made an import function.

[twinnyfo]

I would definitely recommend looking into that. I use an import function rather than a straight import. Your data looks highly un-Normalized and this could save considerable space.

And.... you can practice your coding skills!

:-)

[jimatqsi]

Do you compact the database from time to time? It could be the actual size is less than you think? Look under the Manage option for Compact and Repair (or under tools for older versions of Access)

Jim

[twinnyfo]

Hey, Jim! Good to see you again. Seems like you have been away for a short while. Hope things are going well!

[jforbes]

Haha, Yeah, that would be a lot of records... a lot, sorry for being late to the party Twinnyfo, I spent sometime in Germany for work. It was a blast and they appreciate math. =)

jimatqsi is on it. If you create a lot of records then delete them, your db size wont drop on its own.

Make sure you clear out (delete) all your temp tables after you perform an import. Another guess is that you have some appendix tables that are no longer used. Even at that many records at once a month, your no where near a Gig. I can do the math tomorrow, salesmen permitting. ;)

[twinnyfo]

All,

I am sure there must be "something" creating such a large DB file. But without knowing what data are in the tables, it is all conjecture.

My first thought was (Post #2), as was with many others, to Compact and Repair.

Outside of that, proper Table Structure could help.

@Anoble1, can you post the Table Structure you have for this Table (or tables, as the case may be). Please include the Field name and Data Type. This may help us redirect some structural changes.

[anoble1]

I compact and repair the database a few times a week. I have a feeling something is wrong with these data types. I took over this database and it has always been done with the Access automated import from Excel.

After looking at Capture2. Some of this stuff I am not familiar with, such as Field Size and Format. Sounds like I need to make a new table

[anoble1]

@twinnyfo Wow, found one of my issues. Looked at the structure of the table and went to the properties. There was a filter setup on the properties of the table. Table opens fast now like it should.

[twinnyfo]

anoble1,

Yes, I would definitely recommend creating new tables.

Based on what I see (not what I know) about your tables, it looks like there could be several ways to optimize your Detail Sales Table (which should be several tables).

I'll talk "generics" here, and use what you know about your tables to makes informed decisions.

Let's assume I am keeping track of sales at my business using a DB.

I need a Table called tblCustomers. Everything that I do has to do with customers, so my customers table will include CustomerID, first and last names, business names, contact info and addresses. stuff like that. Anything that is specifically associated with my Customers and is exclusive to one customer only goes in this table.

I also have a table called tblProducts, because what is my business without products to sell, right? This table will include al the information pertaining to the product: Product ID, SKU, Vendor (where I get my products from), Description, Cost (what I pay for it), Price (what I sell it for). Anything that is specifically associated with my products and is exclusive to one product only goes in this table.

Then, I have a Table called tblInvoices. Every time I sell something, I create an invoice. Believe it or not, all we need on this invoice table is an InvoiceID, InvoiceDate and Customer ID. That's all I need. Because.....

I have another table, called tblInvoiceDetails. This table uses the InvoiceID as a Foreign Key, then lists the ProductID and Quantity.

You get the idea. Now, when I want to look at sales invoices, every invoice will have a record (or records) associated witht he invoice details table. Based on those two tables, using join queries, etc., we know the Customer name, address, contact info, Product ID, description, cost, price, total cost (Quantity x Cost) and total Price (quantity x price).

Here is going to be your challenge (and we will be glad to help you along the way): You must import your excel spreadsheet (which I have no doubt that you receive it in a "flat file" like your table), and break that spreadsheet into its various parts (Customers, Products, invoices, Invoice Details etc.) and append those values to your database.

Believe it or not, this actually sounds fun to me--but also a headache at the same time, because it will require many hours of coding, building queries, testing, retesting and testing again, to make sure it works right.

But, again, we're glad to work through these steps with you.

[anoble1]

Yeah, I have other databases like that. I'm not sure if that would be the best thing here though. However, I made a simple import via VBA. Takes a while to import though.

' Open an existing spreadsheet
    Set appExcel = GetObject("C:\Users\anoble\Desktop\PSSR Compensation\PSSR ACCRUAL, BALDWIN ALLOCATION, PARTS COUNTER BONUS\DETAIL SALES UPLOAD\detail sales report-part.xls")
 
    ' Don't show spreadsheet on screen
    'appExcel.Application.Visible = False
 
    Set workSheet = appExcel.Worksheets(1)
 
    i = 2
    While workSheet.Cells.Range("A" & i & ":A" & i).Value <> ""
            sqlStatement = "SELECT tblDetailSales.ID, tblDetailSales.BRANCH, tblDetailSales.LINE, tblDetailSales.ACCOUNT, tblDetailSales.ORDERNum, tblDetailSales.PARTNum, tblDetailSales.QTY, tblDetailSales.DESCRIPTION, tblDetailSales.EXTINVOICEPRICE, tblDetailSales.BDNET, tblDetailSales.BDAVN, tblDetailSales.VENDOR, tblDetailSales.CLASS, tblDetailSales.SOURCE, tblDetailSales.PERIOD, tblDetailSales.CMAST FROM tblDetailSales;"
            Set records = dbs_curr.OpenRecordset(sqlStatement, dbOpenDynaset, dbSeeChanges, dbOptimistic)
            'If Not records.EOF Then
            If Not records.EOF Or True Then
                records.AddNew
                records!BRANCH = workSheet.Cells.Range("A" & i & ":A" & i).Value
                records!Line = workSheet.Cells.Range("B" & i & ":B" & i).Value
                records!ACCOUNT = workSheet.Cells.Range("C" & i & ":C" & i).Value
                records!ORDERNum = workSheet.Cells.Range("D" & i & ":D" & i).Value
                records!PARTNum = workSheet.Cells.Range("E" & i & ":E" & i).Value
                records!QTY = workSheet.Cells.Range("F" & i & ":F" & i).Value
                records!Description = workSheet.Cells.Range("G" & i & ":G" & i).Value
                records!EXTINVOICEPRICE = workSheet.Cells.Range("H" & i & ":H" & i).Value
                records!BDNET = workSheet.Cells.Range("I" & i & ":I" & i).Value
                records!BDAVN = workSheet.Cells.Range("J" & i & ":J" & i).Value
                records!VENDOR = workSheet.Cells.Range("K" & i & ":K" & i).Value
                records!Class = workSheet.Cells.Range("L" & i & ":L" & i).Value
                records!Source = workSheet.Cells.Range("M" & i & ":M" & i).Value
                records!PERIOD = workSheet.Cells.Range("N" & i & ":N" & i).Value
                records!CMAST = workSheet.Cells.Range("O" & i & ":O" & i).Value
 
                records.Update
            'i = i + 1
            End If
            i = i + 1
     Wend
 
    ' Release objects
    Set workSheet = Nothing
    Set workBook = Nothing
    Set appExcel = Nothing
 
    DoCmd.Close acForm, "frmRunningQuery"
    MsgBox "Done"

[twinnyfo]

anoble1,

Have you tried linking the spreadsheet to your DB? This would enable you to use it much like you use a table. This would 1) decomplexify the code tremendously and 2) probably increase the speed.

It also looks like your code is doing a raw import, must the same as what you started with. however, this could be for your initial workings on the code?

[anoble1]

I have used this import in the past. I wonder what other or better VBA options their are?

[twinnyfo]

Like I said, if the spreadsheet is linked, you can create a recordset from the spreadsheet, rather than going line, by line, cell by cell.

BTW, I love your avatar!