Import more than 1 record set @ a time from Access

[gmazza76]

Good Afternoon,

I have created some code to pull a query from Access to Excel which works, but I now want to pull more than 1 query through at a time. So when "Current Week" is selected in a drop down Excel should pull 4 queries into Excel. I am using the following code I have created, but get an error when I try to pull the second query in at "Uploads current week Inbound Calls" I am new at trying to pull data in, but IO have tried a couple of different variations with the same debug error. I am hoping to create 4 optional outcomes so the data can load specific to the criteria requested (current week, last week, last month etc....)

Public cnn As ADODB.Connection
Public sQRY As String
Public strFilePath As String


Sub Import_CurrentWeek()


strFilePath = "\\Source file"


Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
'Uploads current week Process Worked
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strFilePath & ";"
sQRY = "SELECT * FROM CurrentWeekPW"


rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly


Sheet4.Visible = True 'Import
Sheet4.Range("A2").CopyFromRecordset rs
Sheet4.Visible = xlVeryHidden

rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing



'Uploads current week Inbound Calls
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strFilePath & ";"
sQRY = "SELECT * FROM CurrentWeekIC"


rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly


Sheet8.Visible = True 'Import
Sheet8.Range("A2").CopyFromRecordset rs
Sheet8.Visible = xlVeryHidden


rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing


Sheets("Welcome").Select


Exit Sub
End Sub

thanks in advance
Gavin

 

[ParamRay]

Remove the line before the line in red. It should work.

 

[Norie]

No need to close the connection until you've finished with it.

Public cnn As ADODB.Connection
Public sQRY As String
Public strFilePath As String


Sub Import_CurrentWeek()

    strFilePath = "\\Source file"

    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    'Uploads current week Process Worked
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & strFilePath & ";"
    sQRY = "SELECT * FROM CurrentWeekPW"


    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly

    Sheet4.Visible = True    'Import
    Sheet4.Range("A2").CopyFromRecordset rs
    Sheet4.Visible = xlVeryHidden

    rs.Close
    Set rs = Nothing

    sQRY = "SELECT * FROM CurrentWeekIC"

    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly

    Sheet8.Visible = True    'Import
    Sheet8.Range("A2").CopyFromRecordset rs
    Sheet8.Visible = xlVeryHidden

    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing

    Sheets("Welcome").Select

End Sub

 

[gmazza76]

No need to close the connection until you've finished with it.

Public cnn As ADODB.Connection
Public sQRY As String
Public strFilePath As String


Sub Import_CurrentWeek()

    strFilePath = "\\Source file"

    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    'Uploads current week Process Worked
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & strFilePath & ";"
    sQRY = "SELECT * FROM CurrentWeekPW"


    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly

    Sheet4.Visible = True    'Import
    Sheet4.Range("A2").CopyFromRecordset rs
    Sheet4.Visible = xlVeryHidden

    rs.Close
    Set rs = Nothing

    sQRY = "SELECT * FROM CurrentWeekIC"

    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly

    Sheet8.Visible = True    'Import
    Sheet8.Range("A2").CopyFromRecordset rs
    Sheet8.Visible = xlVeryHidden

    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing

    Sheets("Welcome").Select

End Sub

Thank you for the amendments, but I am getting a debug error on the second query "Object Variable or with block variable not set" on the second

rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly

what is it I am missing?

Thanks
Gavin

 

[RoryA]

Remove this line:

Set rs = Nothing

after the first rs.Close line.