Originally Posted by
skydivetom
Darkwind:
thank you for the continued assistance. I appreciate it.
Ok, I added the rs.Close line but now get a different error (3144). Am I still missing something else?
Same bug, different implementation... we need a conditional so the trim query is only executed when there's something to trim...
Code:
Dim bTrimData As Boolean
...
If fld.Type = dbText Then
sSQLCreateTable = sSQLCreateTable & " WITH COMPRESSION,"
sSQLTrimData = sSQLTrimData & "[" & fld.Name & "] = Trim([" & fld.Name & "]),"
bTrimData = True
Else
...
'Trim
If bTrimData Then
Debug.Print sSQLTrimData
db.Execute sSQLTrimData, dbFailOnError
bTrimData = False
End If
Originally Posted by
moke123
Not to muddy the waters, but I'm curious as to the bloat created by creating and deleting the tables.
I would normally be importing the data to a temp table in a temp database and then clean it up when appending to my real tables.
When done I then delete the temp database.
Yeah, deleting and recreating a ton of tables this way is suboptimal, at the least. I would certainly run a compact/repair once done with a big batch of this kind of thing. Overall this solution is fairly messy. If it were my project, I would just fix my original data import to construct the requisite tables WITH COMPRESSION before initial import and split out the date/time in the process. That would mean just creating the tables once, no need for this copy/trim/delete. But that's technically out-of-scope of the request... and at the time I started looking into this thread, I wasn't even aware these were imported tables to begin with.