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

[Guest]

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 :