fb1990
asked on
Importing and Dropping Table in Access
Hello EE,
I have the following code that imports csv files from my directory and creates tables in access. Can someone please help me modify the the code to delete the table(s) if it exist prior to importing the new data? When I run this code multiple time, it creating duplicates in my table
Thanks
I have the following code that imports csv files from my directory and creates tables in access. Can someone please help me modify the the code to delete the table(s) if it exist prior to importing the new data? When I run this code multiple time, it creating duplicates in my table
Thanks
Function DoImport()
Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
blnHasFieldNames = True
strPath = "C:\This\"
strFile = Dir(strPath & "*.csv")
Do While Len(strFile) > 0
strTable = Left(strFile, Len(strFile) - 4)
strPathFile = strPath & strFile
DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames
Kill strPathFile
strFile = Dir()
Loop
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OK,
You need to find out whether a table with the name = strTable already exists. I use a function:
You need to find out whether a table with the name = strTable already exists. I use a function:
Public Function fnTableExists(TableName) as Boolean
Dim intFields as integer
On Error goto ProcError
intFields = currentdb.Tabledefs(TableName).Fields.Count
fnTableExists = true
ProcExit:
Exit Function
ProcError:
fnTableExists = false
End Function
Then, in your code, I would add a line between 15 and 16If fnTableExists(strTable) Then docmd.deleteObject actable, strTable
ASKER
Hello Dale,
DoCmd.RunSQL "Delete * From " & strTable & ";"
Did the trick, but i am not too clear on the implication of a bloated table. I tried your solution and could not get it work. Can you help break it down within the context of my starting code?
And thanks for the help you provided me before.
DoCmd.RunSQL "Delete * From " & strTable & ";"
Did the trick, but i am not too clear on the implication of a bloated table. I tried your solution and could not get it work. Can you help break it down within the context of my starting code?
And thanks for the help you provided me before.
Dale's solution also causes bloat. The point is that Access does NOT recover space from deleted objects without a compact.
If you append 1,000 rows to a table and then delete them, the space they occupied is not freed. If you then append a different 1,000 rows, the space the table occupies is for 2,000 rows. If you delete the table contents and append a new 1,000 rows, the table space now occupies 3,000 rows. That is bloat. Compacting the db frees all that unused space and the table will only occupy 1,000 rows or whatever the actual row content is.
Making a table and deleting it causes exactly the same bloating issue.
If this is a problem for you, I'll post the code from the solution I use. Let me know if you want it.
If you append 1,000 rows to a table and then delete them, the space they occupied is not freed. If you then append a different 1,000 rows, the space the table occupies is for 2,000 rows. If you delete the table contents and append a new 1,000 rows, the table space now occupies 3,000 rows. That is bloat. Compacting the db frees all that unused space and the table will only occupy 1,000 rows or whatever the actual row content is.
Making a table and deleting it causes exactly the same bloating issue.
If this is a problem for you, I'll post the code from the solution I use. Let me know if you want it.
I agree with Pat regarding database bloat. I prefer to link files to my FE (rather than importing them) but since you were importing, I just thought I would go with that. However, whether you link the file to Access or Import it, you will need to determine whether that table name is already in use before you create a new linked table.
The method I mentioned with my fnTableExists() should work, it does for me.
Dale
The method I mentioned with my fnTableExists() should work, it does for me.
Dale
you can set the db property of Compact on Close and not worry about the bloating.
File > Options > Current database > tick the Compact On Close
File > Options > Current database > tick the Compact On Close
ASKER
Thanks Pat. I have used your solution and I will compact on close. Thanks to everyone who contributed. You guys are awesome!
The compact on close solution works as long as the FE is not large or if you are working on a local drive rather than on a server drive. Compacting a large database on the server can be verrrrrrrrrrry slow which is why I don't use that method because it runs EVERY time you close rather than just when you imported a new set of data. Start a new thread if you feel the need to implement my preferred method.
FE are normally on the local drive.
ASKER
the file is small at the moment 150k records and local drive
'/ determine if it exists, and if so then Delete
If DCount("Name", "MSysObjects", "Name='" & strTableName & "'") > 0 Then
DoCmd.DeleteObject acTable, strTableName
End If
You may have better results by linking to the file. You can do that in the External Data >> Import & Link section. If you link the file, you'll always see the most current data.