How can I use 2 diff. recordsets without opening another db connec

[vrk1]

Please go through my code below:

This is my objective: I am trying to execute two different SQL statements
and populate the output of each statement in two worksheets - Sheet1, Sheet2.

I am able to populate Sheet1 with the information. However, I see a blank
Sheet2 when this macro executes.

Can anyone tell me what the problem is?

Thanks much for your time!
________________________________________________________________



Public Sub test()

Dim db As Database, rs As Recordset


Dim targetrange As Range

Set db = OpenDatabase("c:\abc.mdb", False, True, "MS Access;PWD=12345")
Set rs = db.OpenRecordset("select * from goal")


Set targetrange = Range("a1")
Set targetrange = targetrange.Cells(1, 1)


For i = 0 To rs.Fields.Count - 1
targetrange.Offset(0, i).Value = rs.Fields(i).Name
Next


' write recordset
colCounter = 0
rowCounter = 1
targetrange.Offset(rowCounter, colCounter).CopyFromRecordset rs



Sheets("Sheet2").Select

Set rs = db.OpenRecordset("select territory from goal")
Set targetrange = Range("a1")
Set targetrange = targetrange.Cells(1, 1)


For i = 0 To rs.Fields.Count - 1
targetrange.Offset(0, i).Value = rs.Fields(i).Name
Next


' write recordset
colCounter = 0
rowCounter = 1
targetrange.Offset(rowCounter, colCounter).CopyFromRecordset rs
Set rs = Nothing



db.Close
Set db = Nothing
End Sub

[keepITcool]

your main problem is that you select the worksheet,
whereas you need to activate it.

it could/should be done without activating anything:

Public Sub test()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer

Set dbs = DAO.OpenDatabase("c:\abc.mdb", False, True, "MS
Access;PWD=12345")

Set rst = dbs.OpenRecordset("select * from goal")
With ThisWorkbook.Worksheets("sheet1").Range("A1")
'headers
For i = 0 To rst.Fields.Count - 1
.Offset(0, i).Value = rst.Fields(i).Name
Next
'records
.Offset(1, 0).CopyFromRecordset rst
End With

Set rst = dbs.OpenRecordset("select territory from goal")
With ThisWorkbook.Worksheets("sheet2").Range("A1")
'headers
For i = 0 To rst.Fields.Count - 1
.Offset(0, i).Value = rst.Fields(i).Name
Next
' write recordset
.Offset(1, 0).CopyFromRecordset rst
End With

rst.Close: Set rst = Nothing
dbs.Close: Set dbs = Nothing

End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


vrk1 wrote :

> Please go through my code below:
>
> This is my objective: I am trying to execute two different SQL
> statements and populate the output of each statement in two
> worksheets - Sheet1, Sheet2.
>
> I am able to populate Sheet1 with the information. However, I see a
> blank Sheet2 when this macro executes.
>
> Can anyone tell me what the problem is?
>
> Thanks much for your time!
> ________________________________________________________________
>
>
>
> Public Sub test()
>
> Dim db As Database, rs As Recordset
>
>
> Dim targetrange As Range
>
> Set db = OpenDatabase("c:\abc.mdb", False, True, "MS
> Access;PWD=12345") Set rs = db.OpenRecordset("select * from goal")
>
>
> Set targetrange = Range("a1")
> Set targetrange = targetrange.Cells(1, 1)
>
>
> For i = 0 To rs.Fields.Count - 1
> targetrange.Offset(0, i).Value = rs.Fields(i).Name
> Next
>
>
> ' write recordset
> colCounter = 0
> rowCounter = 1
> targetrange.Offset(rowCounter, colCounter).CopyFromRecordset rs
>
>
>
> Sheets("Sheet2").Select
>
> Set rs = db.OpenRecordset("select territory from goal")
> Set targetrange = Range("a1")
> Set targetrange = targetrange.Cells(1, 1)
>
>
> For i = 0 To rs.Fields.Count - 1
> targetrange.Offset(0, i).Value = rs.Fields(i).Name
> Next
>
>
> ' write recordset
> colCounter = 0
> rowCounter = 1
> targetrange.Offset(rowCounter, colCounter).CopyFromRecordset rs
> Set rs = Nothing
>
>
>
> db.Close
> Set db = Nothing
> End Sub
>
>
>