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
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