
December 7th, 2004, 08:28 AM
|
|
Registered User
|
|
Join Date: Sep 2004
Posts: 3
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
Updating Records in .txt file
I want to update some records in text file with data from a OWC spreadsheet but there occurs an error in the '.Execute' statement.
Could anybody help me please?
Here is the code  :
Code:
Private Sub btnUpdate_Click()
Const adCmdText = &H1
Dim i As Long, j As Long
Dim MyWorkbook As Workbook
Dim datConnectiontxt As ADODB.Connection
Dim recSetxt As ADODB.Recordset
Dim strDBtxt, strSQLtxt, strSQLtxt2 As String
'I compare the data from the query against the data of a OWC Spreadsheet
Dim spsUSheet As OWC10.Worksheet
Set spsUSheet = spsUsers.Worksheets(1)
Set MyWorkbook = ThisWorkbook
Set datConnectiontxt = New ADODB.Connection
Set recSetxt = New ADODB.Recordset
'Since this is an app for VBA (Excel), the Users.txt file (DataBase) is in the
'same location
strDBtxt = MyWorkbook.Path
strSQLtxt = "SELECT * FROM Users.txt WHERE [Description] = 'Unknown';"
datConnectiontxt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBtxt & ";" & _
"Extended Properties=""text;HDR=YES;FMT=FixedLenght"""
recSetxt.Open strSQLtxt, datConnectiontxt, adOpenForwardOnly, adLockReadOnly, adCmdText
For i = 2 To spsUSheet.Range("a1").End(xlDown).Row
If IsEmpty(spsUSheet.Cells(i, 2).Value) Or IsNull(spsUSheet.Cells(i, 2).Value) GoTo Sig
End If
'In the line below occurs the error
strSQLtxt2 = "UPDATE Users.txt SET [Description] = " & spsUSheet.Cells(i, 2).Value _
& " WHERE [UserID] = " & spsUSheet.Cells(i, 1).Value & ";"
datConnectiontxt.Execute strSQLtxt2
Sig:
Next i
closefrmUsers
End Sub
I'd appreciate any help.
Thanx in advance
|