Connection/Table name keeps changing

[JEQ214]

Hello,

I'm working in Excel 2007 and I have a command button that changes the criteria of a sql from an Access DB based on some checkboxes. I can't run it more than once or it will change the Connection and Table names from "Top15Office" to "Top15Office_1" and give me a "Subscript out of Range" error.

        sConn = "DSN=MS Access Database;DBQ=\\nv-mc044100-dmr\4100\CRE.mdb;DefaultDir=\\nv-mc044100-dmr\4100\;"
        sConn = sConn & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
 
        'Office
        Set oCn = New ADODB.Connection
        oCn.ConnectionString = sConn
        oCn.Open
 
        sSQL = "SELECT Top 15 qryComl_Conc_Top15_Office.NAME, qryComl_Conc_Top15_Office.`ACCT#`, " & _
        "qryComl_Conc_Top15_Office.`BANK COMMIT`, qryComl_Conc_Top15_Office.PD_LGD_LEG " & _
        "FROM `\\nv-mc044100-dmr\4100\CRE.mdb`.qryComl_Conc_Top15_Office qryComl_Conc_Top15_Office " & _
        "WHERE (qryComl_Conc_Top15_Office.BUS_LINE In (" & Criteria & ")) " & _
        "ORDER BY qryComl_Conc_Top15_Office.`BANK COMMIT` DESC"
 
        Set oRS = New ADODB.Recordset
        oRS.Source = sSQL
        oRS.ActiveConnection = oCn
        oRS.Open
 
        ActiveWorkbook.Connections("Top15Office").Delete
        Sheet25.QueryTables("Top15Office").Delete
        Range("A8:D23").ClearContents
        Set oQt = Worksheets(6).QueryTables.Add(Connection:=oRS, Destination:=Range("A8:D23"))
            oQt.Name = "Top15Office"
            oQt.PreserveFormatting = True
            oQt.RefreshOnFileOpen = False
            oQt.RefreshStyle = xlOverwriteCells
            oQt.AdjustColumnWidth = False
            oQt.Refresh
 
        ActiveWorkbook.Connections("Connection").Name = "Top15Office"

The errors occur here:

        ActiveWorkbook.Connections("Top15Office").Delete
        Sheet25.QueryTables("Top15Office").Delete

How do I keep it from adding that "_1" at the end?

Jon

 

[Norie]

Jon

Why are you adding a query table?

You've already established a connection to the database and opened a recordset.

Why not just copy the records from that recordset to the worksheet?

If you do need to add the query table then when it comes to delete it refer to it by it's index.

Assuming there is only one query table that should be 1.

You can use the same idea for the connection.

 

[JEQ214]

Why not just copy the records from that recordset to the worksheet?

How do I do that? Sorry, my vba isn't that great. It took me awhile researching bits of this code and applying it to my own.

If you do need to add the query table then when it comes to delete it refer to it by it's index.

Assuming there is only one query table that should be 1.

There are multiple query tables. How do I know which one to reference? Is there a function to get the index of a query table?

Thanks for your help

Jon

 

[Norie]

Jon

I was just posting about the query table and was going to post about how you can get the data without one.

I'll just post how to get the data without one and if you want I'll come back to the query table.

This is how you could put the data from the recordset on a worksheet, it assumes you already opened the recordset.

Dim rngDst As Range
Dim fld As ADODB.Field
Dim I As Long
 
' code to get recordset
 
 
   Set rngDst = Worksheets(6).Range("A8")
 
   For Each fld In oRS.Fields
            rngDst.Offset(,I).Value = fld.Value
            I=I+1
   Next fld
 
   rngDst.Offset(1).CopyFromRecordSet oRS

One of the things I was going to suggest for the query table probem was to not try and delete the connection.

 

[JEQ214]

Thanks Norie! That worked and it was much faster, but there were some formatting problems. I applied the code to two of my tables which are next to each other; A8:D23 and G8:J23.

-row 8 no longer has field names. it copies the first record
-the header for the 2nd table shifted over 4 cells to K8:N8, but resumes the rest of the recordset in G9:J23. 2nd table also replaced headers with a copy of the 1st record.



This works much better than what I originally had, btw. Thanks

 

[JEQ214]

I figured it out Norie, but thanks again for everything!

 

[Norie]

Just out of curiosity how did you fix the original problem?

 

[JEQ214]

It wasn't really resolved. I took your advice and just copied the recordset to the worksheet instead of deleting/adding the querytables/connections, which was causing the problem.