Editing or adding a record

S

SAC

I'm trying to edit a record.

First I connecto to an excel spreadsheet and then to a sql server table via
odbc.

I load the first row of the spreadsheet, find the record in the table, if it
exists. If it exists, then I want to edit it. If it doen's exist I want to
add it.

Here's my code so far. This is just a small test of the edit. It doesn't
like this.

Also when it cannot find a record in the table I get an error about eof or
bof. Not quite sure how to handle that.

Thanks for your help.

Option Compare Database
Option Explicit
Function BPCSConvertTWDECH()
DoCmd.SetWarnings 0
Dim strSQL As String
Dim cntmp As ADODB.Connection
Dim cn As ADODB.Connection
Dim rstmp As New ADODB.Recordset
Dim rs As New ADODB.Recordset
Set cntmp = New ADODB.Connection
Set cn = New ADODB.Connection
With cntmp
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\Documents and Settings\Administrator\My
Documents\Database\Trans\060326\TWDECH.xls; ReadOnly=True;"
.Open
End With

cn.Open "PROVIDER=MSDASQL;" & _
"DSN=TWHQ;DATABASE=TWSQL;" & _
"UID=Sam;PWD=39sogwap53;"

Set rstmp = cntmp.Execute("SELECT * FROM [Database Query Results$]")

Do While Not rstmp.EOF
Set rs = cn.Execute("SELECT * FROM TWDECH WHERE [HORD] = " & rstmp!HORD
& " Order by Hord;")

'If it's found I want to update it
'*****************************************
'Trying to edit a record
'
'
'
rs.Fields("HORD").Value = rstmp!HORD 'This doesn't work..............
rs.Update


'If it doesn't exit I want to add it
'???????????????


rstmp.MoveNext
Loop

cntmp.Close
cn.Close

Set rs = Nothing
Set rstmp = Nothing
Set cn = Nothing
Set cntmp = Nothing


End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top