Correct Syntax

  • Thread starter Thread starter GLT
  • Start date Start date
Hi Gents,

Thanks for your reply - I have each text file loaded into a memo feild and
then read the memo feild line by line, and extract the feild information from
each line into a new table.

I then scan the table and insert three unique values for each set of data:
(1) JobNo, (2) Session No (3) Workstation ID - this allows each record to be
unique.

I thought that maybe at this point (because I can identify each record
uniquely), I might be able to use SQL to load into a new table, but this also
will not work.

So now I am trying to read each line, check what the feild is and then
insert feild value in a new record. Each Set of data begins at the Job No
record, so i am trying to loop through the data (and update each feild in the
new table) until it hits a new Job no, then repeats this process over again...

This is the code that I am using to try to acheive this:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim strDate As String
Dim strFileNo As String
Dim strFeild1 As String
Dim strFeild2 As String

Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset("tbl_ParsedLogFileDetails")
Set rst1 = CurrentDb.OpenRecordset("tbl_BackupLogStats")

If rst.RecordCount = 0 Then
'rst.Close
Set rst = Nothing
Set db = Nothing
Else
With rst
.MoveLast
.MoveFirst

With rst1

Do Until rst.EOF

strDate = rst!DateStamp
strFileNo = rst![FileNo]
strFeild1 = rst![Feild1]
strFeild2 = rst![Feild2]

Debug.Print rst![Session] & " " & rst![JobNo]


If strFeild1 = "Session" Then

rst1.Edit
rst1![Session] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Job No" Then

rst1.AddNew
rst1![FileNo] = rst![FileNo]
rst1![DateStamp] = rst![DateStamp]
rst1![JobNo] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Job ID" Then

rst1.AddNew
rst1![Job ID] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Description" Then

rst1.Edit
rst1![Description] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Workstation" Then

rst1.Edit
rst1![Workstation] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Source" Then

rst1.Edit
rst1![Source] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Target" Then

rst1.Edit
rst1![Target] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Start Time" Then

rst1.Edit
rst1![Start Time] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Directories" Then

rst1.Edit
rst1![Total Directories] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total File(s)" Then

rst1.Edit
rst1![Total File(s)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Skip(s)" Then

rst1.Edit
rst1![Total Skip(s)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Size (Disk)" Then

rst1.Edit
rst1![Total Size (Disk)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Size (Media)" Then

rst1.Edit
rst1![Total Size (Media)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Elapsed Time" Then

rst1.Edit
rst1![Elapsed Time] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Session Status" Then

rst1.Edit
rst1![Session Status] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Average Throughput" Then

rst1.Edit
rst1![Average Throughput] = strFeild2
rst1.Update
rst.MoveNext

If rst![Feild1] = "Total Error(s)/Warning(s)" Then

rst1.Edit
rst1![Total Error(s)/Warning(s)] = strFeild2
rst1.Update

ElseIf rst![Feild1] = "Job No" Then

rst1.Update
rst.MovePrevious

End If

Else

rst.MoveNext

End If

Loop

End With

End With

rst.Close
rst1.Close

End If


I'm still working / testing the above code - if you know of any better ways
to acheive this then Im all ears...

Cheers,
GLT


The
 
I have each text file loaded into a memo feild and
then read the memo feild line by line, and extract the feild
information from each line into a new table.

I then scan the table and insert three unique values for each set
of data: (1) JobNo, (2) Session No (3) Workstation ID - this
allows each record to be unique.
Click to expand...

Why do you need to scan the resulting table to do that? Why can't
you calculate those values and do the update with SQL?
I thought that maybe at this point (because I can identify each
record uniquely), I might be able to use SQL to load into a new
table, but this also will not work.
Click to expand...

Parsing data that's in a record does not require walking through
each record one at a time.
 
Some off-hand comments on your code follow.

This is the code that I am using to try to acheive this:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim strDate As String
Dim strFileNo As String
Click to expand...

You should probably do a Search & Replace to correct the misspelling
of strField1 and strField2:
Dim strFeild1 As String
Dim strFeild2 As String

Set db = CurrentDb()
Click to expand...

Why assign this database variable and then never use it?

Instead of:
Set rst = CurrentDb.OpenRecordset("tbl_ParsedLogFileDetails")
Set rst1 = CurrentDb.OpenRecordset("tbl_BackupLogStats")
Click to expand...

It should logically be:

Set rst = db.OpenRecordset("tbl_ParsedLogFileDetails")
Set rst1 = db.OpenRecordset("tbl_BackupLogStats")
If rst.RecordCount = 0 Then
Click to expand...

Your logic is backwards here. You will need to close the recordset
and de-initialize the object variables at the end, regardless of
whether your recordset returns records, so your If/Then/Else logic
should more logically be:

If rst.RecordCount <> 0 Then
[process the recordset]
End If
rst.Close
Set rst = Nothing
rst1.Close
Set rst = Nothing
set db = Nothing

(since you're using CurrentDB(), you can't close your db, though it
doesn't produce an error -- why have a line of code that does
absolutely nothing?)
'rst.Close
Set rst = Nothing
Set db = Nothing
Else
With rst
Click to expand...

You're not using the .RecordCount for anything but determining if
your recordset has records in it, so a .MoveLast is just wasteful --
you don't need it at all.
.MoveLast
.MoveFirst
Click to expand...

It's dangerous to nest WITH blocks like this. And I think it
produces hard-to-understand code when you're working with two
different recordsets. Indeed, you don't actually take advantage of
your WITH blocks, so you really ought to remove them entirely.
With rst1

Do Until rst.EOF

strDate = rst!DateStamp
strFileNo = rst![FileNo]
strFeild1 = rst![Feild1]
strFeild2 = rst![Feild2]
Click to expand...

The brackets above are unnecessary.
Debug.Print rst![Session] & " " & rst![JobNo]


If strFeild1 = "Session" Then

rst1.Edit
rst1![Session] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Job No" Then

rst1.AddNew
rst1![FileNo] = rst![FileNo]
rst1![DateStamp] = rst![DateStamp]
rst1![JobNo] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Job ID" Then

rst1.AddNew
rst1![Job ID] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Description" Then

rst1.Edit
rst1![Description] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Workstation" Then

rst1.Edit
rst1![Workstation] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Source" Then

rst1.Edit
rst1![Source] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Target" Then

rst1.Edit
rst1![Target] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Start Time" Then

rst1.Edit
rst1![Start Time] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Directories" Then

rst1.Edit
rst1![Total Directories] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total File(s)" Then

rst1.Edit
rst1![Total File(s)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Skip(s)" Then

rst1.Edit
rst1![Total Skip(s)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Size (Disk)" Then

rst1.Edit
rst1![Total Size (Disk)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Size (Media)" Then

rst1.Edit
rst1![Total Size (Media)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Elapsed Time" Then

rst1.Edit
rst1![Elapsed Time] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Session Status" Then

rst1.Edit
rst1![Session Status] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Average Throughput" Then

rst1.Edit
rst1![Average Throughput] = strFeild2
rst1.Update
rst.MoveNext
Click to expand...

The following If block looks out of place, not just because of the
indentation, but because it tests the value in rst what was used to
assign the value of strFeild1.
If rst![Feild1] = "Total Error(s)/Warning(s)"
Then

rst1.Edit
rst1![Total Error(s)/Warning(s)] = strFeild2
rst1.Update

ElseIf rst![Feild1] = "Job No" Then
Click to expand...

This puzzles me. I assume that the fact that it's back to "Job No"
means that you're on the next record. But why would you
..MovePrevious?
rst1.Update
rst.MovePrevious

End If

Else

rst.MoveNext

End If

Loop

End With

End With

rst.Close
rst1.Close

End If


I'm still working / testing the above code - if you know of any
better ways to acheive this then Im all ears...
Click to expand...

Is it not the case that you're entire complicated If/Then/ElseIf
look could be replaced with:

rst1.Edit
rst1(strFeild1) = strFeild2
rst1.Update
rst.MoveNext

I just don't see why you need to test for the values of strField1,
which supplies only the field name. Given that every single one of
your tests was testing strField1 and then using the literal value
that was tested for in the If condition to determine the field that
got assigned strFeild2's value, it seems the the code should be much
simpler.

Question:

Does every record have the same number of lines in it?

I'm assuming your incoming data looks something like this:

Session: [data]
Job No: [data]
Job ID: [data]
[more fields]
Session: [data]
Job No: [data]
Job ID: [data]
[more fields]

etc.

If so, why not replace Session: with <CrLf> + Session: so then all
your records would be separated by a blank line. Then you could
replace <CrLf> + <CrLf> with some token (e.g., @#@, i.e., something
that's not going to occur in your data), then replace all single
<CrLf> with tabs, then replace the token with <CrLf>. You'd end up
with a file that has each record on one line. You wouldn't be able
to actually strip out the field names, but you could then process
the data with SQL instead of by walking recordsets (you'd use
Instr(), Mid(), Left() and Right() to get the job done).

I've done a lot of imports of unstructured data, and this is how
I've always done it. If it needs to be done frequently, it can be
automated. When I'm doing a one-off, I just do it in Word! But it
could just as easily be done with standard file I/O by reading the
file contents into a string variable and then doing a Replace() on
its contents.

Of course, I've assumed that none of the fields have <CrLf> embedded
in them. If that's the case, you'd need to parse the data
differently, such as not replacing <CrLf> alone, but doing it
fieldname by fieldname (e.g., <CrLf>Job No: replaced by <Tab>Job
No:, and so forth).

I really think it's a vastly superior method, as the string
replacement functions are going to be much faster than constant
updating of recordsets, field-by-field, row-by-row.
 
Hi David,

Thanks for your reply:
Is it not the case that you're entire complicated If/Then/ElseIf
look could be replaced with:

rst1.Edit
rst1(strFeild1) = strFeild2
rst1.Update
rst.MoveNext
Click to expand...

The above is what I initially tried to do before I wrote all the IF
statements:

rst1!strReadFeild1 = strFeild2 <----- Item not found error

If I try your code above, I get the following error on this line:

rst1(strFeild1) = strFeild2

Item not found in this collection - it seems that I cannot use a string
varible as a name for a feild.

If I could I'd rather use something like your code, its much shorter and
more effieicent; but everytime I tried something like that it would produce
errors.

If I am doing something wrong with your code above, please let me know.

Cheers,
GLT.



David W. Fenton said:
Some off-hand comments on your code follow.

This is the code that I am using to try to acheive this:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim strDate As String
Dim strFileNo As String
Click to expand...

You should probably do a Search & Replace to correct the misspelling
of strField1 and strField2:
Dim strFeild1 As String
Dim strFeild2 As String

Set db = CurrentDb()
Click to expand...

Why assign this database variable and then never use it?

Instead of:
Set rst = CurrentDb.OpenRecordset("tbl_ParsedLogFileDetails")
Set rst1 = CurrentDb.OpenRecordset("tbl_BackupLogStats")
Click to expand...

It should logically be:

Set rst = db.OpenRecordset("tbl_ParsedLogFileDetails")
Set rst1 = db.OpenRecordset("tbl_BackupLogStats")
If rst.RecordCount = 0 Then
Click to expand...

Your logic is backwards here. You will need to close the recordset
and de-initialize the object variables at the end, regardless of
whether your recordset returns records, so your If/Then/Else logic
should more logically be:

If rst.RecordCount <> 0 Then
[process the recordset]
End If
rst.Close
Set rst = Nothing
rst1.Close
Set rst = Nothing
set db = Nothing

(since you're using CurrentDB(), you can't close your db, though it
doesn't produce an error -- why have a line of code that does
absolutely nothing?)
'rst.Close
Set rst = Nothing
Set db = Nothing
Else
With rst
Click to expand...

You're not using the .RecordCount for anything but determining if
your recordset has records in it, so a .MoveLast is just wasteful --
you don't need it at all.
.MoveLast
.MoveFirst
Click to expand...

It's dangerous to nest WITH blocks like this. And I think it
produces hard-to-understand code when you're working with two
different recordsets. Indeed, you don't actually take advantage of
your WITH blocks, so you really ought to remove them entirely.
With rst1

Do Until rst.EOF

strDate = rst!DateStamp
strFileNo = rst![FileNo]
strFeild1 = rst![Feild1]
strFeild2 = rst![Feild2]
Click to expand...

The brackets above are unnecessary.
Debug.Print rst![Session] & " " & rst![JobNo]


If strFeild1 = "Session" Then

rst1.Edit
rst1![Session] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Job No" Then

rst1.AddNew
rst1![FileNo] = rst![FileNo]
rst1![DateStamp] = rst![DateStamp]
rst1![JobNo] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Job ID" Then

rst1.AddNew
rst1![Job ID] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Description" Then

rst1.Edit
rst1![Description] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Workstation" Then

rst1.Edit
rst1![Workstation] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Source" Then

rst1.Edit
rst1![Source] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Target" Then

rst1.Edit
rst1![Target] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Start Time" Then

rst1.Edit
rst1![Start Time] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Directories" Then

rst1.Edit
rst1![Total Directories] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total File(s)" Then

rst1.Edit
rst1![Total File(s)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Skip(s)" Then

rst1.Edit
rst1![Total Skip(s)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Size (Disk)" Then

rst1.Edit
rst1![Total Size (Disk)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Total Size (Media)" Then

rst1.Edit
rst1![Total Size (Media)] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Elapsed Time" Then

rst1.Edit
rst1![Elapsed Time] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Session Status" Then

rst1.Edit
rst1![Session Status] = strFeild2
rst1.Update
rst.MoveNext

ElseIf strFeild1 = "Average Throughput" Then

rst1.Edit
rst1![Average Throughput] = strFeild2
rst1.Update
rst.MoveNext
Click to expand...

The following If block looks out of place, not just because of the
indentation, but because it tests the value in rst what was used to
assign the value of strFeild1.
If rst![Feild1] = "Total Error(s)/Warning(s)"
Then

rst1.Edit
rst1![Total Error(s)/Warning(s)] = strFeild2
rst1.Update

ElseIf rst![Feild1] = "Job No" Then
Click to expand...

This puzzles me. I assume that the fact that it's back to "Job No"
means that you're on the next record. But why would you
..MovePrevious?
rst1.Update
rst.MovePrevious

End If

Else

rst.MoveNext

End If

Loop

End With

End With

rst.Close
rst1.Close

End If


I'm still working / testing the above code - if you know of any
better ways to acheive this then Im all ears...
Click to expand...

Is it not the case that you're entire complicated If/Then/ElseIf
look could be replaced with:

rst1.Edit
rst1(strFeild1) = strFeild2
rst1.Update
rst.MoveNext

I just don't see why you need to test for the values of strField1,
which supplies only the field name. Given that every single one of
your tests was testing strField1 and then using the literal value
that was tested for in the If condition to determine the field that
got assigned strFeild2's value, it seems the the code should be much
simpler.

Question:

Does every record have the same number of lines in it?

I'm assuming your incoming data looks something like this:

Session: [data]
Job No: [data]
Job ID: [data]
[more fields]
Session: [data]
Job No: [data]
Job ID: [data]
[more fields]

etc.

If so, why not replace Session: with <CrLf> + Session: so then all
your records would be separated by a blank line. Then you could
replace <CrLf> + <CrLf> with some token (e.g., @#@, i.e., something
that's not going to occur in your data), then replace all single
<CrLf> with tabs, then replace the token with <CrLf>. You'd end up
with a file that has each record on one line. You wouldn't be able
to actually strip out the field names, but you could then process
the data with SQL instead of by walking recordsets (you'd use
Instr(), Mid(), Left() and Right() to get the job done).

I've done a lot of imports of unstructured data, and this is how
I've always done it. If it needs to be done frequently, it can be
automated. When I'm doing a one-off, I just do it in Word! But it
could just as easily be done with standard file I/O by reading the
file contents into a string variable and then doing a Replace() on
its contents.

Of course, I've assumed that none of the fields have <CrLf> embedded
in them. If that's the case, you'd need to parse the data
differently, such as not replacing <CrLf> alone, but doing it
fieldname by fieldname (e.g., <CrLf>Job No: replaced by <Tab>Job
No:, and so forth).

I really think it's a vastly superior method, as the string
replacement functions are going to be much faster than constant
Click to expand...
 
Hi David,

Thanks for your reply:


The above is what I initially tried to do before I wrote all the
IF statements:

rst1!strReadFeild1 = strFeild2 <----- Item not found error

If I try your code above, I get the following error on this line:

rst1(strFeild1) = strFeild2

Item not found in this collection - it seems that I cannot use a
string varible as a name for a feild.
Click to expand...

Yes, of course you can. When the error comes up, check the value of
strFeild1 and see if it's the actual name of one of the fields in
rst1. The error is telling you that there is no field in the
recordset with the name that's stored in your variable.

There's absolutely nothing unusual about the syntax I gave, using a
variable for the field name. I've coded that kind of thing literally
100s of times.
If I could I'd rather use something like your code, its much
shorter and more effieicent; but everytime I tried something like
that it would produce errors.

If I am doing something wrong with your code above, please let me
know.
Click to expand...

Run the code and when the error comes up, check the value of the
variable. Either the recordset you defined left out some of the
fields, or the fields do not have the same name in both recordsets.
 
Hi David,

Thanks for your reply - It was a feild in the New table I had spelt
incorrectly - your code works perfectly - A BIG THANKYOU...

Cheers,
GLT.
 
Back
Top