parse tables

[George]

I need to parse each Access table named r*.
The structure of r* tables is (I'm interested only in "F2" field) :
create
change
delete
At least one of blocks must not be empty, for example:
table "r071203_xlsx"
create
6026001
change
7399003
delete

(each block can either be empty or to consist of 1 or more values)

So my need is to parse all "r*" tables, find 7-digit value(s) in each
of
blocks and to write new table:
field1: name of "r*" table
field2: 7-digit value
field3: create, change or delete
Please, be so kind, tell me how do I perform this?
Thank you in advance.

 

[John W. Vinson]

I need to parse each Access table named r*.
The structure of r* tables is (I'm interested only in "F2" field) :
create
change
delete
At least one of blocks must not be empty, for example:
table "r071203_xlsx"
create
6026001
change
7399003
delete

(each block can either be empty or to consist of 1 or more values)

So my need is to parse all "r*" tables, find 7-digit value(s) in each
of
blocks and to write new table:
field1: name of "r*" table
field2: 7-digit value
field3: create, change or delete
Please, be so kind, tell me how do I perform this?
Thank you in advance.

I take it that these are (originally or now) Excel spreadsheets? Access
doesn't *HAVE* a "F2" field. And your example is frankly incomprehensible.

What is in fact the structure of your table? What is a "block" (Access doesh't
HAVE blocks)? What's the relationship between the seven digit numbers and the
"change" etc. values?

For that matter... what are you trying to accomplish, in a real-world sense?
How are these _xlsx tables created?

 

[George]

John, ok, you're right, that was Excel file originally. I've imported
files into Access tables:

Sub test()
Dim rstCurr As DAO.Recordset
Dim dbsCurr As Database
Dim MyPath As String
Dim MyFile As String
Dim ShortFile As String
MyPath = "C:\Marchello\"
Set dbsCurr = Access.CurrentDb
Set rstCurr = dbsCurr.OpenRecordset("Table1", dbOpenDynaset)
MyFile = Dir(MyPath)
Do While MyFile <> ""
rstCurr.AddNew
rstCurr.Fields("1").Value = Time$
rstCurr.Fields("2").Value = Date$
rstCurr.Fields("3").Value = MyPath
rstCurr.Fields("4").Value = MyFile
rstCurr.Update
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
MyFile, MyPath & MyFile
MyFile = Dir
Loop

I thought it'll be easier to work in Access later without dealing
with Excel.

Please find example of table here (printscreen) :
http://marchello.ccx-grads.org/example.jpg

I say "block" when I mean part of table, for example, after word
"create", it can be empty (no value was created this time) or can
consist of 1 or more values.

7-digit value is management accounting code.

In other words, my task is to prepare a list _when_ values was
created, changed or deleted.
Each name of Excel files (now it's Access table) includes date:
r071203_xlsx is the 3th of December, 2007.

I have to parse about thousand tables. Please, be so kind, suggest me
way of solution.

 

[John W. Vinson]

Please find example of table here (printscreen) :
http://marchello.ccx-grads.org/example.jpg

I say "block" when I mean part of table, for example, after word
"create", it can be empty (no value was created this time) or can
consist of 1 or more values.

7-digit value is management accounting code.

In other words, my task is to prepare a list _when_ values was
created, changed or deleted.
Each name of Excel files (now it's Access table) includes date:
r071203_xlsx is the 3th of December, 2007.

I have to parse about thousand tables. Please, be so kind, suggest me
way of solution.

ow... that's ugly, I fear. Your answer depends on position of records in
tables, and tables don't HAVE (usable) positons.

I would suggest that you repost in an Excel group. Once this data has been
imported into Access I fear it will be much more difficult to accomplish; it
would need VBA to step through the records, and would be very inefficient.

Maybe someone else can suggest something, but it looks like a major pain to
me.

 

[George]

John, thank you for your reply.

I'm sorry I bother you, could you please take a look at this stuff
again.

I've got solution (below).
Now I have to merge these two modules into one;
temporary table for importing Excel files must be the one;
I have to add the name of each spreadsheet into table named
tblOutput;
drop all tables except tblOutput after work.

I can't know all names of tables with import errors (description of 7-
digit values is rather too long to import - or maybe it's possible to
imagine how to import long descriptions without errors? but it's not
so useful), otherwise I would use sql with DROP.

Please, be so kind, suggest me the way of solution.
Thanks ahead.

http://marchello.ccx-grads.org/solution.txt

Sub test()

Dim rstCurr As DAO.Recordset
Dim dbsCurr As Database
Dim MyPath As String
Dim MyFile As String
Dim ShortFile As String
MyPath = "C:\Marchello\"
Set dbsCurr = Access.CurrentDb
Set rstCurr = dbsCurr.OpenRecordset("Table1", dbOpenDynaset)
MyFile = Dir(MyPath)
Do While MyFile <> ""
rstCurr.AddNew
'rstCurr.Fields("1").Value = Time$
'rstCurr.Fields("2").Value = Date$
'rstCurr.Fields("3").Value = MyPath
'rstCurr.Fields("4").Value = MyFile
'rstCurr.Update
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, MyFile,
MyPath & MyFile
MyFile = Dir
Loop

End Sub

Sub test()

Dim db As DAO.Database, tdf As DAO.TableDef
Dim strAction As String
Dim rstIn As DAO.Recordset, rstOut As DAO.Recordset

' Point to this database
Set db = CurrentDb
' Open the output recordset
Set rstOut = db.OpenRecordset("tblOutput", _
dbOpenDynaset, dbAppendOnly)
' Loop through all tabledefs
For Each tdf In db.TableDefs
' Look for a table name starting with "r"
'If tdf.Name Like "r*" Then
If Left(tdf.Name, 1) = "r" Then
' Found one - open it
Set rstIn = db.OpenRecordset("SELECT F2 " & _
"FROM [" & tdf.Name & "] " & _
"WHERE Len(F2 & """") > 0")
' Process all the records
Do Until rstIn.EOF
' See if keyword
If (rstIn!F2 = "Create") Or (rstIn!F2 = "Change") _
Or (rstIn!F2 = "Delete") Then
' Just save the action
strAction = rstIn!F2
Else
' Make sure we have a good action
If Len(strAction) > 0 Then
' Write an output record
rstOut.AddNew
rstOut!Field1 = tdf.Name
rstOut!Field2 = rstIn!F2
rstOut!Field3 = strAction
rstOut.Update
End If
End If
' Get the next record
rstIn.MoveNext
Loop
' Close the input
rstIn.Close
End If
' Get the next table
Next tdf
' Clean up
rstOut.Close
Set rstIn = Nothing
Set rstOut = Nothing
Set tdf = Nothing
Set db = Nothing

End Sub