Openrecordset

[reta]

Hi i am creating a db that connects to another accees db, in a different location and they may not be connected at all times. Therefore, i created a button in which allows them to connect to my db and compares their data to my db and updates or inserts records into my matser db.
The problem is that we are unable to get the recordset working, below is the code that refers to the button that connects to my master db.
Thanks for you help

Private Sub Command0_Click()
Dim WS As Workspace
Dim db As Database
Dim cn As rdoConnection
Dim rs As Recordset 'Our database
Dim rs2 As Recordset ' HeadOffice database



Set WS = CreateWorkspace("", "admin", "", dbUseJet)
Set db = WS.OpenDatabase("P:\TM reports access\DealerVisitReport.mdb", True)

strSql = "select * from DealerVisitReport"
Set rs2 = db.OpenRecordset(strSql)' this is the problem.!!!!

'rs2.Open strSql2, db.Connection, adOpenKeyset, adLockOptimistic
Set rs = New Recordset
strSql = "select * from [fakequery]"
rs.Open strSql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

MsgBox " it worked!!!"



End Sub

 

[PHV]

we are unable to get the recordset working
Any error message ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[reta]

yes
run-time error '13'
Type mismatch
This error is sooo broad that i have no idea what to do

 

[PHV]

Try to replace this:
Dim rs2 As Recordset
By this:
Dim rs2 As DAO.Recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[reta]

i tried that and it said that my db was in use, but knowone is using it.... weird

 

[mik18]

You need to establish the connection before you open the recordset.
Set cn = CurrentProject.Connection

mike

 

[reta]

i tried to set the connection however the same error came up that the "P:\TM reports access\DealerVisitReport.mdb" is in use and it highlights
rs.Open strSql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
this as the error
Reta

 

[connectionscentres]

Hey Reta,

I have EXACTLY the same problem when trying to copy parts of a query to an Excel workbook. Here is my code:

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
objXL.Application.workbooks.Add
Dim objActiveWkb As Object
Set objActiveWkb = objXL.Application.ActiveWorkBook

Dim db As Database
Dim rs As Recordset
Dim strSQLNew As String
Set db = CurrentDb
strSQLNew = "SELECT * FROM QUERY_COMMS;"
Set rs = db.OpenRecordset(strSQLNew, 2)

I get the type mismatch error.

I have tried changing recordset into dao.recordset but each time it crashes Access!! Weird.

Also, how do you "set the connection"? I dont think I have done that.

Cheers.