Merging Dbase3 dbf files to create single access database table. 2

[CliffordTheRedDog]

I am still in the process of converting our company steel control to Access from dbase3. As the data changes on a hourly basis i have set up macros to copy the data over when we go live. There is one area that currently is a problem due to the way the Dbase database files have been set up. We have 1995 (*.dbf) files that contain the Parts List data for each product. we have a dbase macro (prg)that will compile a new table, but this requires manually inputting the 1995 product names.
I have a text list for (dabase3) databases IE
a.dbf
b.dbf
c.dbf etc

These databases consist of two fields each Partnum Quantity examples shown below.
a.dbf
Partnum Quantity
1024 2
1025 3
1026 1

b.dbf
1024 1
1027 1

c.dbf
1022 1
1023 1
1024 5

I need to transfer all the contents to a single Access 2002 database with three fields Product Partnum Quantity Product being taken from the dbf name. The final result would look like this

Product Partnum Quantity
a 1024 2
a 1025 3
a 1026 1
b 1024 1
b 1027 1
c 1022 1
c 1023 1
c 1024 5
etc

is there a way to do this?
TIA
Cliff

 

[CliffordTheRedDog]

A few more clues...
I have limited the number of transferred files (by reducing the ProductPartsList)to three BA3FL BA4FL & BA7FL to limit errors just for the time being.
The location table has propogated as shown below. It has created three products 144 records.

The location table contains 55 Entries for BA3FL each file has been added 9 times which is the amount of parts in the original BA3FL.dbf I have shown the entire contents of the three products, and the first few of the location table. The linked dbf files in the table view increment from ProductParts to ProductParts1, ProductParts2. I suspect this might be because the delete table is not working correctly in the module. I did change the line
(If err.Number = "3265" Then) to 3265 and of course the error returned as before. (i have changed it back!)

If the table is not being deleted, is that why there are multiple linked tables being created or would you have expected that anyway? i hope this makes some sense to you

er, help?

BA3FL.DBF
PARTNUM QUANTITY
14057 1
14117 1
14152 1
14169 1
14173 1
14174 1
14176 1
14714 1
14716 1
BS4FL.DBF
PARTNUM QUANTITY
14067 1
14135 1
14141 1
14152 1
14170 1
14173 1
14174 1
14177 1
14639 1
BS7FL.DBF
PARTNUM QUANTITY
14087 1
14144 1
14145 1
14152 2
14172 1
14173 1
14174 1
14179 1
14718 1

Product Partnum Quantity
BA3FL 14057 1
BA4FL 14057 1
BA7FL 14057 1
BA3FL 14057 1
BA4FL 14057 1
BA7FL 14057 1
BA3FL 14057 1
BA4FL 14057 1
BA7FL 14057 1
BA3FL 14057 1
BA3FL 14057 1
BA4FL 14057 1
BA7FL 14057 1
BA3FL 14057 1
BA4FL 14057 1
BA7FL 14057 1
BA3FL 14117 1
BA4FL 14117 1
BA7FL 14117 1
BA3FL 14117 1
BA4FL 14117 1
BA7FL 14117 1
BA3FL 14117 1
BA4FL 14117 1
BA7FL 14117 1
BA3FL 14117 1
BA3FL 14117 1
BA4FL 14117 1
BA7FL 14117 1
BA3FL 14117 1
BA4FL 14117 1
BA7FL 14117 1

Thanks for you persistance,
Cliff

 

[JonFer]

1. Your append query may have an extraneous table in it. It should only be based on ProductParts - I have the example above. In query design, you should only see ProductParts in the upper half. Try doing the module logic manually for two tables to see if you get any problems - link one dbf as ProductParts, run the append, delete the ProductParts, link a second dbf, run the append. It should not be generating a Cartesian product.

Another possibility is the ProductParts table is not getting deleted so it is appending the first dbf's records over and over with different Products. When you link a table with a name already in the database, it appends the sequential numbers you were seeing (ProductParts1, ProductParts2, etc). After removing those extra tables, run the module with just a few dfs in the list and see if you have multiple ProductParts tables.

2. You were right, the error checking line should be without quotes -> If Err.Number = 3265 Then ...

3. You can add this line BEFORE the While loop to clear out "Location Table" before the appending starts. Then you won't have to do it manually.

DoCmd.RunSQL "Delete * from [Location Table]"

4. To delete the tables rapidly, hide the tables you don't want to delete (click the "Hidden" checkbox on the table properties window), turn off the "Document Deletion" verification option in Options, then go to the Table tab and hold down the delete button. When done, turn on the delete verification option again and unhide your tables (Options/View/Hidden to see them). You can also export just what you want to a new DB - probably safer this way.

5. Remember to put a primary key on "Location Table" when done. This will prevent duplicates for any later data entry. I'd hold off for now until you get the module working correctly. Then add the primary key and run it once more.

 

[CliffordTheRedDog]

Jonfer,
Thanks for your reply,


I have run the command as is and it currently appends the contents of table product parts to all the the tables names that follow on ie BA3FL BA4FL BA7FL (these all contain ProductParts parts as it has not effectivly been deleted)

I agree the crux of the problem lies with the fact that the delete table module call does not in fact delete the ProductParts table; could this be because allthough the table has been renamed ProductParts, MSAcces will still see it as the linked tablename ie BA3FL. So when the module calles for deletion of the table ProductParts it fails! (I will change the delete command in the module to reflect the name of the linked table ie BA3FL to see if this works- if it does i will be left with the problem of how to word the coding to delete the table from which the contents have just been added to the location table)()

Step 1
Append query has one table in the upper section. To ensure all links are correct i removed the table re-added it with the link to productparts and re-added the two parts of the query in the bottom section that removed with the table. the sql is this:

PARAMETERS ProductCode Text ( 255 );
INSERT INTO [Location Table] ( Product, Partnum, Quantity )
SELECT [ProductCode] AS Expr1, ProductParts.PARTNUM, ProductParts.QUANTITY
FROM ProductParts;

Step 2
The product parts is definately not getting deleted i made an error in the last report the following line:
(If err.Number = "3265" Then) to 3265 and of course the error returned as before. (i have changed it back!)

Should have read:
(If err.Number = "3265" Then) to (If err.Number = "3264" Then) and of course the error returned as before. (i have changed it back!)

this was to let you know that the error code' 3265 - Table not found but okay to continue ) was working and although allowing the rest of the routine to proceed as planned - still left the productparts table intact therefore the loop created poductparts1 productparts2 etc.


When i manually run the append the pruduct code is some kind of system name i have cut n pasted it here for you to see.

{90150409-6000-11D3-

This is how the relocation table looks when i have run the append twice, linking BA3FL and BA4FL as directed. i have checked the contents and other than the system No the contents are correct for the two products!

Product Partnum Quantity
{90150409-6000-11D3- 14087 1
{90150409-6000-11D3- 14117 1
{90150409-6000-11D3- 14714 1
{90150409-6000-11D3- 14716 1
{90150409-6000-11D3- 14169 1
{90150409-6000-11D3- 14152 1
{90150409-6000-11D3- 14173 1
{90150409-6000-11D3- 14057 1
{90150409-6000-11D3- 14176 1
{90150409-6000-11D3- 14179 1
{90150409-6000-11D3- 14144 1
{90150409-6000-11D3- 14145 1
{90150409-6000-11D3- 14718 1
{90150409-6000-11D3- 14172 1
{90150409-6000-11D3- 14152 2
{90150409-6000-11D3- 14173 1
{90150409-6000-11D3- 14174 1
{90150409-6000-11D3- 14174 1

Step 3
works great, thankyou.

Step 4
Great tip, have not tried this yet, no doubt may need this later.

Step 5
Primary key to prevent duplicates - will do later as you suggest.

Thanks
Cliff

 

[CliffordTheRedDog]

Jonfer,
I have been looking at the delete parts of the module and i was inspired by your:

DoCmd.RunSQL "Delete * from [Location Table]"

so i replaced the problem line
' db1.TableDefs.Delete "ProductParts"with
DoCmd.DeleteObject acTable, "ProductParts" and it works, i will test it fully and let you know the "final" results.

Thanks again
Cliff



Sub ImportDbase3List()

Dim db1 As DAO.Database
Dim rsDbfTables As DAO.Recordset
Dim qdAppend As DAO.QueryDef
Dim strProduct As String
Dim strDir As String, strFileName As String

Set db1 = CurrentDb
Set qdAppend = db1.QueryDefs("qryAppendProductParts&quot
Set rsDbfTables = db1.OpenRecordset("ProductPartsList&quot

DoCmd.RunSQL "Delete * from [Location Table]"


' look through list of dbf files
While Not rsDbfTables.EOF


' delete ProductParts table (linked for creation of append query) but continue if not found
On Error GoTo err_NoTable
' db1.TableDefs.Delete "ProductParts"
DoCmd.DeleteObject acTable, "ProductParts"

On Error GoTo 0

' get data from dbf list (filename, directory name, determine product code)
strDir = rsDbfTables!DirectoryName
strFileName = rsDbfTables!FileName
strProduct = Left(strFileName, InStr(strFileName, ".&quot - 1)

' link DBASEIII table but call it "ProductParts"
DoCmd.TransferDatabase acLink, "DBASE III", strDir, acTable, strFileName, "ProductParts"

' set query parameter named "ProductCode" (created in Query Design)
' to strProduct (derived from filename above)
qdAppend!ProductCode = strProduct
' run query
qdAppend.Execute

rsDbfTables.MoveNext

Wend

Exit Sub

err_NoTable:

' 3265 - Table not found but okay to continue
If Err.Number = 3264 Then

Resume Next
Else
MsgBox "Error: " & Err.Number & " - " & Err.Description
End If


End Sub

 

[JonFer]

Did you know you can use F8 to step through each line of the module? If you position the cursor over a variable, it will display the variable value at that point in time. This should help determine what is going on.

For example, after you run the link step you can switch over to the "Tables" tab of the database window and see what the linked table name is. After the assignment of strProduct, you can see what its new value is.

 

[CliffordTheRedDog]

Jonfer,
Thanks I didn't know about the f8!

I have learnt a lot though on this project. I will not get another chance to work on this until Wednesday. Thanks again, you have been a great help!

Cliff

 

[CliffordTheRedDog]

Jonfer,
Have run it a couple of times and it workes just fine. Am tidying up other aspects of the DataBase now.
Thanks for all your help.
Cliff