opening recordsets in forms coding

[Guest]

I wish to compare two tables and based on the existance of data I want to
insert data into table 2.. I am using the following code for this. but It
says type mismatch near set rs1 = "select ..."

Pls help

Private Sub CmdImpDB_Click()
Dim SQL As String
Dim cn As Connection
Dim rs As Recordset
Dim rs1 As Recordset

Set cn = CurrentDb.Connection
Set rs1 = "select * from tbl_wc_wcands_kpwcmatch"

Do While rs1.EOF = False
SQL = rs1!REC_ISN
Set rs = "select * from kpwcmaster,tbl_wc_wcands_kpwcmatch where
kpwcmaster.rec_isn = '" & SQL & "'"
If rs.EOF Then
DoCmd.RunSQL "insert into kpwcmaster select * from
tbl_wc_wcands_kpwcmatch where rec_isn='" & SQL & "'"

End If
rs1.MoveNext
Loop


End Sub

Madhuri

 

[Guest]

Try this, see if that helps

Private Sub CmdImpDB_Click()
Dim SQL As String
Dim MyDB As Database
Dim rs As Recordset
Dim rs1 As Recordset

Set MyDB = CodeDb()
Set rs1 = MyDB.openRecordset ("select * from tbl_wc_wcands_kpwcmatch")

While not rs1.EOF
SQL = rs1!REC_ISN
Set rs = MyDB.openRecordset ("select * from kpwcmaster,
tbl_wc_wcands_kpwcmatch where kpwcmaster.rec_isn = '" & SQL & "'")
If rs.EOF Then
DoCmd.RunSQL "insert into kpwcmaster select * from
tbl_wc_wcands_kpwcmatch where rec_isn='" & SQL & "'"

End If
rs1.MoveNext
Wend


End Sub

 

[Guest]

Thanks Ofer

I dont know what went wrong with me today. I should have defined recordset
with respect to the connection. My problem is that I am a vb programmer and
my current project wants me to work with msaccess as a front end which I get
upset about. But thats ok...

Could you please help me by referring any links which could offer me help on
MS Access as front end and SQL Server as backend ?

And also let me know whether the forms I am developing in access could be
used both for msaccess and SQL server as backend.

Thanks
Madhuri

 

[Guest]

You can use google to serch for the subject "Access with Sql Server"

And yes, access could be used both for msaccess and SQL server as backend.