Whats wrong with ADO code

M

Mohan

Hi
I am using textimport to import a large file from text into Access table
(which is working fine). Then I use DAO to select all the records just
imported (which will have last update time stamp empty) to update the time
stamp using the follwoing code: (I get invalid argument error)

Private Sub cmdImport_Extract_Click()
Dim lst_updt_time As Date
lst_updt_time = Now()

On Error GoTo ErrorCheck

DoCmd.TransferText acImportFixed, "EX_GL_DATA_SPEC", "EX_GL_data",
varfilename, False


Me.lblMessage.Caption = "Importing new data from CSV file"

Dim db2 As DAO.Database
Dim rstTrGL As DAO.Recordset

Set db2 = CurrentDb
strSQL_TRGL = "SELECT Property_Number, Property_No, Accounting_Date,
Acct_Date, Post_Amount, Post_Amount_Sign, Post_Amount2, lst_updt_timestamp
FROM EX_GL_data WHERE lst_updt_timestamp is null "


Set rstTrGL = db2.OpenRecordset(strSQL_TRGL)
If Not (rstTrGL.EOF = True Or rstTrGL.BOF = True) Then
rstTrGL.MoveFirst
End If


Do While rstTrGL.EOF = False
rstTrGL.Edit
rstTrGL!lst_updt_timestamp = lst_updt_time
rstTrGL.Update
rstTrGL.MoveNext

If rstTrGL.EOF = True Then
rstTrGL.MoveLast
Exit Do
End If

Loop

Me.lblMessage.Caption = rstTrGL.RecordCount & " - Records imported
sucessfully"
Set rstTrGL = Nothing
Set db2 = Nothing

Exit Sub
ErrorCheck:
Select Case Err.Number
Case 7874 'Import file not there to delete
Resume Next
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Exit Sub
End Select
 
M

Morris

Hi
I am using textimport to import a large file from text into Access table
(which is working fine).  Then I use DAO to select all the records just
imported (which will have last update time stamp empty) to update the time
stamp using the follwoing code:  (I get invalid argument error)

Private Sub cmdImport_Extract_Click()
Dim lst_updt_time As Date
lst_updt_time = Now()

On Error GoTo ErrorCheck

DoCmd.TransferText acImportFixed, "EX_GL_DATA_SPEC", "EX_GL_data",
varfilename, False

Me.lblMessage.Caption = "Importing new data from CSV file"

Dim db2 As DAO.Database
Dim rstTrGL As DAO.Recordset

Set db2 = CurrentDb
   strSQL_TRGL = "SELECT Property_Number, Property_No,  Accounting_Date,
Acct_Date, Post_Amount, Post_Amount_Sign, Post_Amount2, lst_updt_timestamp
FROM EX_GL_data WHERE lst_updt_timestamp is null "

 Set rstTrGL = db2.OpenRecordset(strSQL_TRGL)
    If Not (rstTrGL.EOF = True Or rstTrGL.BOF = True) Then
    rstTrGL.MoveFirst
    End If

Do While rstTrGL.EOF = False
     rstTrGL.Edit
     rstTrGL!lst_updt_timestamp = lst_updt_time
     rstTrGL.Update
     rstTrGL.MoveNext

     If rstTrGL.EOF = True Then
     rstTrGL.MoveLast
     Exit Do
     End If

Loop

Me.lblMessage.Caption = rstTrGL.RecordCount & " -  Records imported
sucessfully"
Set rstTrGL = Nothing
Set db2 = Nothing

 Exit Sub
ErrorCheck:
    Select Case Err.Number
        Case 7874   'Import file not there to delete
            Resume Next
        Case Else
            MsgBox "Error " & Err.Number & " - " & Err.Description
            Exit Sub
    End Select
Click to expand...

My guess (but it's just a guess) is that there is type incompatibility
between the recordset value and your variable.

lst_updt_time is Date
but rstTrGL!lst_updt_timestamp is probably something else

do you think that might be the reason?

Morris
 
J

J_Goddard via AccessMonster.com

Hi -

Which line is generating the invalid argument error? The only thing I can
think of is that in the TransferTest command - where is varfilename defined?



John
 
D

Dirk Goldgar

Mohan said:
Hi
I am using textimport to import a large file from text into Access table
(which is working fine). Then I use DAO to select all the records just
imported (which will have last update time stamp empty) to update the time
stamp using the follwoing code: (I get invalid argument error)

Private Sub cmdImport_Extract_Click()
Dim lst_updt_time As Date
lst_updt_time = Now()

On Error GoTo ErrorCheck

DoCmd.TransferText acImportFixed, "EX_GL_DATA_SPEC", "EX_GL_data",
varfilename, False


Me.lblMessage.Caption = "Importing new data from CSV file"

Dim db2 As DAO.Database
Dim rstTrGL As DAO.Recordset

Set db2 = CurrentDb
strSQL_TRGL = "SELECT Property_Number, Property_No, Accounting_Date,
Acct_Date, Post_Amount, Post_Amount_Sign, Post_Amount2, lst_updt_timestamp
FROM EX_GL_data WHERE lst_updt_timestamp is null "


Set rstTrGL = db2.OpenRecordset(strSQL_TRGL)
If Not (rstTrGL.EOF = True Or rstTrGL.BOF = True) Then
rstTrGL.MoveFirst
End If


Do While rstTrGL.EOF = False
rstTrGL.Edit
rstTrGL!lst_updt_timestamp = lst_updt_time
rstTrGL.Update
rstTrGL.MoveNext

If rstTrGL.EOF = True Then
rstTrGL.MoveLast
Exit Do
End If

Loop

Me.lblMessage.Caption = rstTrGL.RecordCount & " - Records imported
sucessfully"
Set rstTrGL = Nothing
Set db2 = Nothing

Exit Sub
ErrorCheck:
Select Case Err.Number
Case 7874 'Import file not there to delete
Resume Next
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Exit Sub
End Select
Click to expand...


You don't say what line is giving you the error, which would help. However,
it seems to me that you can simplify your life enormously and gain
efficiency in the process, by using a simple update query instead of looping
through your recordset,. How about:

'----- start of code -----
With CurrentDb

.Execute _
"UPDATE EX_GL_data SET lst_updt_timestamp = Now() " & _
"WHERE lst_updt_timestamp Is Null", _
dbFailOnError

Me.lblMessage.Caption = .RecordsAffected & _
" - Records imported sucessfully"

End With
'----- end of code -----

That would replace all your recordset code.
 
C

Chris

Check your table, EX_GL_data , and make sure that the lst_updt_timestamp
field is set to a date/time data type. In my experience, data imported from a
text file will be stored as a text data type by default, as a result, date
values will not act the way you expect until you change the data type of the
field to date/time. A possible work around would be to set up a table with
the field names and data types that you wish to use. Then import the data to
a temporary table and run an append query to insert the data into your
permanent table with the data types defined how you want them.
 
M

Mohan

Hi
I am just trying with the last update time stamp filed. but I need to
update few other files as well (so reccord set is really needed)
Ex: I have few data fields that was imported from the text file that are
fixed length
i.e Feb 08, 2008 is given as 20080208 (whouth the dashes) so I had to import
this into a text field now I need to update a new fileld with data formate
Also, the signate of the dollar amount (positve or negative is given in a
separate field. So I need to multiply the dollar amount by -1 if the sign =
"-" then update a new filed with in double type with the signage

So I bring all the fields that I need to update (as well as the original
fileld) change the format and then update the new fileld

The error is generated on the rstTrGL.update line The transfer text is
working fine and I can see the recordset (rstTrGL) values in the immediate
window so the record set selection is also fine.. Only when I update the
error is generated.

Here is the entire code:

Private Sub cmdImport_Extract_Click()

Dim lst_updt_time As Date
lst_updt_time = Now()
Dim strSQL1 As String

Me.lblMessage.Caption = "File import process started, Please wait..."

Me.txt_csvfilepath.SetFocus
If IsNull(Me.txt_csvfilepath.Value) Then
MsgBox "Please select a file from the list", vbOKOnly + vbExclamation, "ERROR"
Exit Sub
End If

On Error GoTo ErrorCheck
' validation 1 - make sure the file path is not empty or null

varfilename = Me.txt_csvfilepath
If IsNull(varfilename) Then
MsgBox "You must enter an input filename.", vbExclamation, " "
Me.txt_csvfilepath.SetFocus
Exit Sub
End If

' validation 2 - make sure the file really exists (if the user typed the
full path)

If Dir(varfilename) = "" Then
MsgBox "The file specified does not exists", vbOKOnly + vbExclamation,
"ERROR"
Me.txt_csvfilepath.SetFocus
Exit Sub
End If


' get Excalibur databased path
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim ExcalibrDBpath As String

ExcalibrDBpath = ""
Set db = CurrentDb
strSQL = "SELECT top 1 Def_Value FROM utl01t_defaults WHERE Def_name =
'EX_data_full_path'"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
ExcalibrDBpath = rs.Fields("Def_Value")

rs.Close
Set db = Nothing



'Import the new data from CSV file
Me.lblMessage.Caption = "Importing new data from CSV/TXT file"


DoCmd.TransferText acImportFixed, "EX_GL_DATA_SPEC", "EX_GL_data",
varfilename, False


Me.lblMessage.Caption = "Importing new data from CSV file"


'delete the trailer record from the table
strSQL2 = "DELETE FROM [EX_GL_data] WHERE Investor_Number = '999999999'"
Me.lblMessage.Caption = "Delete the trailer record from the table"
DoCmd.RunSQL strSQL2 & ";"


Me.lblMessage.Caption = "File import sucessfull"


''=== use the dao method to update last update time stamp & other date
fields , amounts with signage ===========

Dim db2 As DAO.Database
Dim rstTrGL As DAO.Recordset
Dim MyProperty_Number As Long
Set db2 = CurrentDb
strSQL_TRGL = "SELECT Property_Number, Property_No, Accounting_Date,
Acct_Date, Post_Amount, Post_Amount_Sign, Post_Amount2, lst_updt_timestamp
FROM EX_GL_data WHERE lst_updt_timestamp is null "


Set rstTrGL = db2.OpenRecordset(strSQL_TRGL)
If Not (rstTrGL.EOF = True Or rstTrGL.BOF = True) Then
rstTrGL.MoveFirst
End If


Do While rstTrGL.EOF = False
rstTrGL.Edit

MyProperty_Number = rstTrGL!Property_Number
Accounting_Date = rstTrGL!Accounting_Date
Post_Amount = rstTrGL!Post_Amount
Post_Amount_Sign = rstTrGL!Post_Amount_Sign

New_Acct_Date = CDate(Mid(Accounting_Date, 5, 2) & "/" &
Right(Accounting_Date, 2) & "/" & Left(Accounting_Date, 4))

rstTrGL!Property_No = CLng(MyProperty_Number) ' chang to long data type
rstTrGL!Acct_Date = New_Acct_Date ' changed to date type
rstTrGL!lst_updt_timestamp = lst_updt_time '(already date type)

If Post_Amount_Sign = "-" Then
rstTrGL!Post_Amount2 = Post_Amount * -1
Else
rstTrGL!Post_Amount2 = Post_Amount
End If

rstTrGL!lst_updt_timestamp = lst_updt_time
rstTrGL.Update

rstTrGL.MoveNext

If rstTrGL.EOF = True Then
rstTrGL.MoveLast
Exit Do
End If


Loop

Me.lblMessage.Caption = rstTrGL.RecordCount & " - Records imported
sucessfully"
Set rstTrGL = Nothing
Set db2 = Nothing

Exit Sub

'======= dao method end =============
ErrorCheck:
Select Case Err.Number
Case 7874 'Import file not there to delete
Resume Next
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
Exit Sub
End Select

End sub
 
M

Mohan

THe transfertext is working fine

THe error is generated on the update of the record set.

The last update time stamp is not imported from the text file... I update
this with Now() , this filed is date/time data type.

I have posted the entire code with reply to Dirk

Thanks
 
D

Dirk Goldgar

Mohan said:
I am just trying with the last update time stamp filed. but I need to
update few other files as well (so reccord set is really needed)
Click to expand...

I don't see why. What's wrong with:

'----- start of code -----
With CurrentDb

.Execute _
"UPDATE EX_GL_data SET " & _
"Property_No = CLng(MyProperty_Number), " & _
"Acct_Date = CDate(" & _
"Mid(Accounting_Date, 5, 2) & '/' & " & _
"Right(Accounting_Date, 2) & '/' & " & _
"Left(Accounting_Date, 4)), " & _
"lst_updt_timestamp = Now(), " & _
"Post_Amount_2 = " & _
"IIf(Post_Amount_Sign = '-', " & _
"-PostAmount, PostAmount) " & _
"WHERE lst_updt_timestamp Is Null", _
dbFailOnError

Me.lblMessage.Caption = .RecordsAffected & _
" - Records imported sucessfully"

End With
'----- end of code -----

But if you don't want to go that route, we can try to figure out what's
wrong with your recordset version. You said you got an "invalid argument"
error on the line
rstTrGL.Update
Click to expand...

Right? That seems an odd error to get. What was the exact error number and
message, please? Although I see a few unnecessary things in your code, I
don't see why you would get an error. The only obvious thing to check is
that the fields that are receiving these updates are all of the correct data
types, and even if they weren't, I'd expect you to get a different error
from what you report.
 
M

Mohan

Hi

Yes.. I seee that your method is much simpler...

I figured out the problem (or at least I got it working) by
creating a workspace and opening the DB on that workspace (instead of
linking the table and setting the db = currentdb)

Here is what I did and now its working fine

Dim wrkJet as Wokspace
Set wrkJet = CreateWorkspace("","admin","", dbUseJet)

Dim db2 as DAO.database
Dim rstExGL as dao.Recorset

set db2 = wrkJet.Opendatabase(ExcaliburDBpath, false, false, "MS
Access;pwd=mypassword")

'note: ExcaliburDBpath is already obtained from the parameter table in
earlier code
' mypassword is also set with the correct password to the database

Do While rstExGl.EOF = false
rstExGL.edit

rstExl!Property_No = clng(rstExGl!Property_Number)

rstExGl.update
rstExGl.MoveNext

if rstExGl.EOF = true then
rstExGl.MoveLast
Exit DO
end if

Loop
 
Top