Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    5

    Unanswered: Sub will not run - not sure why?

    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!
    -Terry



    ListWorksheets ("c:\temp\inworktemp.xls")

    TableCleanup



    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

    With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & strFullName & ";" & _
    "Extended Properties=""Excel 8.0; HDR=No; IMEX=1; """
    .Open
    End With

    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)

    Else

    wkshtname = "INVALID"
    Form_Form1.List1.AddItem tbl.Name

    End If

    If wkshtname <> "INVALID" Then
    strSQL = "INSERT INTO Table1 (F1) IN 'c:\test\worksheetnames.mdb' SELECT * FROM [" & wkshtname & "B4:B]"
    Debug.Print strSQL
    cnn.Execute strSQL
    Else
    End If

    Next tbl

    cnn.Close
    Set cat = Nothing
    Set cnn = Nothing

    End Sub


    Public Sub TableCleanup()

    DoCmd.RunSQL "DELETE Table1.*, Table1.F1 FROM Table1 WHERE (((Table1.F1) Is Null or (Table1.F1) = ' '));"

    End Sub
    Last edited by Syvman; 02-17-06 at 18:05.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What happens when you run that query normally, as in create a new query and run it manually?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2006
    Posts
    5
    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!

  4. #4
    Join Date
    Feb 2006
    Posts
    5
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •