Here's an odd one I've been trying to figure out for a while now. The following code imports the data out of a single column of an excel spreadsheet into my access table (it loops through each worksheet within the spreadsheet). That is what the ListWorksheets subprocedure does. But after that, I am trying to run another subprocedure - TableCleanup - that executes a delete query (RunSQL command) that deletes all null values and spaces in the table. Here's what's going wrong... The import works fine, but I end up with over 17000 records, most of which are null (there are only about 600 actual cells with data in the spreadsheet). The RunSQL statement doesn't seem to run. Well, I lied - it runs on very FEW occasions. This is really odd - it seems the TableCleanup will run at random times - but not all the time. Could anyone have any idea why this would happen? The only idea I have is that it may not be finished running the import before it tries to run my delete query, so it just ignores it. But I highly doubt that it would just ignore a subprocedure or any of its statements. But I put the same delete query as the click event on a command button, and ran it separately, and it performs the deletes fine. I'm really stumped.
Thanks in advance!
Public Sub ListWorksheets(ByRef strFullName As String)
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim wkshtname As String
Set cnn = New ADODB.Connection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strFullName & ";" & _
"Extended Properties=""Excel 8.0; HDR=No; IMEX=1; """
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn
DoCmd.RunSQL ("DELETE * FROM Table1") 'empty table
For Each tbl In cat.Tables
' Worksheet names always end in the "$" character.
If Right(tbl.Name, 1) = "$" Then
Form_Form1.List1.AddItem Left(tbl.Name, Len(tbl.Name) - 1) 'strip the $ from tbl.Name
wkshtname = tbl.Name
ElseIf Right(tbl.Name, 2) = "$'" Then
Form_Form1.List1.AddItem Mid(tbl.Name, 2, Len(tbl.Name) - 2) 'strip both single quotes and the $
wkshtname = Mid(tbl.Name, 2, Len(tbl.Name) - 2)
I've got the same query built and it runs fine by itself. That's what has me so stumped. I basically built the query and then cut/pasted the SQL into my RunSQL statement in the TableCleanup Sub. I also took that same statement and placed it into the click event of a bogus command button. That works fine, too... BUT - it doesn't work right away. I have to wait a couple of seconds (this is a very large spreadsheet that its processing). If I open the form and run the procedure that calls ListWorksheets and TableCleanup, I have to wait about three seconds after ListWorksheets is initiated. Then if I run the query (either manually through my built queries list or by my command button), it works fine and removes all the nulls/spaces. Could it be a problem with the cnn.Execute statement I'm using?
I apologize if I'm not making much sense - I've only been dealing with Access for a few months now... I thought I was doing okay until today, haha. Thanks for your reply!
I've made some progress... Well, not really progress, but I have determined part of my problem. It has something to do with the append query I'm using (INSERT INTO)... If I remove the path that it's referencing (IN 'c:\test\blah.xls'), I receive an error, telling me that it cannot locate Table1. It appears that this is because I'm running the execute statement against the cnn (in this case, cnn is the connection to the Excel sheet via ADO). SO, my NEW question is, how do I append the data that is selected from the spreadsheet without using the Append query I've coded? I don't think I can build an append query to do this, because it would have to be attached to the cnn connection. Thanks for reading my mess!