4 tables into one

[rainbows]

i have 4 tables with a folderpath in them. this takes reports to the respective places on the server


table name folderpath

pdffolder \\192.168.4.16\contdocs\sales\orders\current orders
pdffolderpd \\192.168.4.16\contdocs\production\current orders
pdffoldere \\192.168.2.14\contdocs\engineering \bom
pdffolderp \\192.168.4.16\contdocs\purchasing\purchase orders

the part code below is for the first folderpath " pdffolder"

although this works ok , is it the correct way to do this or should all 4 be in one table ? if it should how do i get the code to look at the correct row in the table

thanks steve

 On Error GoTo Err_Handler
 
    Const FOLDER_EXISTS = 75
    Const MESSAGE_TEXT1 = "No current invoice."
    Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
    Dim strFullPath As String
    Dim varfolder As Variant
 
   If Not IsNull(Me.id) Then
        ' build path to save PDF file
       ' varFolder = "C:\Users\User\Documents"
        varfolder = DLookup("Folderpath", "pdfFolder")
        If IsNull(varfolder) Then

 varfolder = varfolder & "\" & year(date) & "\" & [Order Details subform]![CustomerName] & "\" & "NCO" & Format(Me.NCO_No, "0000")

 

[Pat Hartman]

So, each of the four tables has ONE row with a path in it? If so, the answer is yes, one table would be better.

 

[Gasman]

You create another field that holds the data you have shown prior to the IP address, then DLookUp that record.

 

[The_Doc_Man]

although this works ok , is it the correct way to do this or should all 4 be in one table ?

This is a common question for folks new to Access or to relational databases in general. When you have four things that vary only in the details of their content, and the tables used to store the four things have the same fields, there is a very large (99.9+%) chance that you should merge the four tables into one and add a field to differentiate them. Not 100% because just as soon as we make a hard-and-fast rule, someone will find one of the few exceptions. But I can't offhand think of a reason to NOT combine the tables you have described. You could then write a loop to send out the four reports sequentially, stepping through the four records one at a time when that is appropriate, or using a DLookup for times when a single report is desired.

Based on this combined-table concept, your differentiating field might contain "Sales", "Production", "Engineering", and "Purchasing" (or abbreviations thereof) as the way to tell the four entries apart. Look at it this way... doing it all with a single table and a record for each case vs. doing it with individual single-row tables, how much trouble would it be to add reports for "Marketing" and "Quality Control"? Create two new tables and add the logic to hit each one, or add two new records and allow that loop that used to hit the four entries in turn to now hit six entries and NO change to your sequencer code?

 

[JeanMarc22]

I like the answer from The_Doc_Man.
The one exception I have made to split tables of similar data is when the database was near 2GB.
I split it on a field that is a primary filter. Then when you query your data then you can pick the query based on your filter. The field could be an added field that segregate your data.
Backend SQL server has good features for partitioning tables.

 

[Pat Hartman]

Once your BE approaches 2GB, the best solution is SQL Server. Splitting tables into sections or just splitting by whole table and putting some in one BE and others in another, eliminates your ability to use Referential Integrity which is a big part of the benefit of using an RDBMS to begin with. NO relationships can be defined between separate BE's

I will say that 30 years ago I worked with an order entry database at Reader's Digest. They had over 60 million customers at the time so the "table" was pretty big even by mainframe standards. The transaction tables were monsters. The customer "table" was split into 10 sections using the first character of the last name to identify which "table" a customer might be found in. They split the transaction table using the same rule so they could maintain RI. They were also split by year so I think only the past 5 years was in the "active" "table". The rest of the data was archived. But the key was that all the tables were actually in the same database so RI could be used.

This also happened to be where I built my first Access database so I was using DB2 on the IBM mainframe as the BE and huge tables from the beginning back in the early 90's. I was sold on Access as a tool.

 

[The_Doc_Man]

Backend SQL Server also can handle more than 2 GB, thus negating the need for a split. However, the "bigger backend than 2 GB" problem HAS been seen around here.