Access DB too large, ideas?

[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 tblInvoiceDetai ls. 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!