Convert fieldnames to records in multiple tables into one table (inter database)

  • Thread starter Thread starter dean.brunne
  • Start date Start date
D

dean.brunne

Hi- We have a third party software for forecasting called ForecastPro
that has an ODBC connection that outputs to our Access database. The
output format has the first field as an INDEX and the remaining fields
as the various product names. There are 20 tables in the same format
not all with the same products. They each have a total field.

I need to create one table for all of this output that has the
following fields:

Customer - name of the table (20 tables each represent different
customers)
Index - from the Index field. (straight forward)
Product - from the fieldnames of each table converted to a record
Baseline Units - the records in the product fields.

I would like to create code to loop through the tables and insert into
one table in another database. I am unsure how to structure the
INSERT INTO sql and unsure how to do it inter database.

Any suggestions would be appreciated.

Cheers,

Dean
 
In
dean.brunne@ said:
Hi- We have a third party software for forecasting called ForecastPro
that has an ODBC connection that outputs to our Access database. The
output format has the first field as an INDEX and the remaining fields
as the various product names. There are 20 tables in the same format
not all with the same products. They each have a total field.

I need to create one table for all of this output that has the
following fields:

Customer - name of the table (20 tables each represent different
customers)
Index - from the Index field. (straight forward)
Product - from the fieldnames of each table converted to a record
Baseline Units - the records in the product fields.

I would like to create code to loop through the tables and insert into
one table in another database. I am unsure how to structure the
INSERT INTO sql and unsure how to do it inter database.

Any suggestions would be appreciated.
Click to expand...

Here's some untested, recordset-looping "air code" to process a single
table whose name is passed to the function:

'----- start of "air code" -----
Sub NormalizeProductTable(TableName As String)

' add your own error-handling, of course

Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim fld As DAO.Field

Dim varIndex As Variant

With CurrentDb
Set rsIn = .OpenRecordset(TableName)
' Use your output table name in the following:
Set rsOut = .OpenRecordset("ProductUnits")
End With

With rsIn
Do Until .EOF
varIndex = !INDEX
For Each fld in .Fields
If fld.Name <> "INDEX" _
And fld.Name <> "Total" _
Then
rsOut.AddNew
rsOut!Customer = TableName
rsOut!Index = !INDEX
rsOut!Product = fld.Name
rsOut![Baseline Units] = fld.Value
rsOut.Update
End If
Next fld
.MoveNext
Loop
.Close
End With

rsOut.Close

Set rsIn = Nothing
Set rsOut = Nothing

End Sub
'----- end of "air code" -----

If I understood you correctly, that ought to be something along the
lines of what you need. You'd just need to call it for each of the
twenty tables, passing the table name each time.
 
In




dean.brunne@ said:
Hi- We have a third party software for forecasting called ForecastPro
that has an ODBC connection that outputs to our Access database. The
output format has the first field as an INDEX and the remaining fields
as the various product names. There are 20 tables in the same format
not all with the same products. They each have a total field.
Click to expand...
I need to create one table for all of this output that has the
following fields:
Click to expand...
Customer - name of the table (20 tables each represent different
customers)
Index - from the Index field. (straight forward)
Product - from the fieldnames of each table converted to a record
Baseline Units - the records in the product fields.
Click to expand...
I would like to create code to loop through the tables and insert into
one table in another database. I am unsure how to structure the
INSERT INTO sql and unsure how to do it inter database.
Click to expand...
Any suggestions would be appreciated.
Click to expand...

Here's some untested, recordset-looping "air code" to process a single
table whose name is passed to the function:

'----- start of "air code" -----
Sub NormalizeProductTable(TableName As String)

' add your own error-handling, of course

Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim fld As DAO.Field

Dim varIndex As Variant

With CurrentDb
Set rsIn = .OpenRecordset(TableName)
' Use your output table name in the following:
Set rsOut = .OpenRecordset("ProductUnits")
End With

With rsIn
Do Until .EOF
varIndex = !INDEX
For Each fld in .Fields
If fld.Name <> "INDEX" _
And fld.Name <> "Total" _
Then
rsOut.AddNew
rsOut!Customer = TableName
rsOut!Index = !INDEX
rsOut!Product = fld.Name
rsOut![Baseline Units] = fld.Value
rsOut.Update
End If
Next fld
.MoveNext
Loop
.Close
End With

rsOut.Close

Set rsIn = Nothing
Set rsOut = Nothing

End Sub
'----- end of "air code" -----

If I understood you correctly, that ought to be something along the
lines of what you need. You'd just need to call it for each of the
twenty tables, passing the table name each time.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Dirk,

Thanks, I wasn't expecting specific code. I probably misused the term
inter database as it is from one database to another. The 20 tables
are in a standalone db and I want to use this code in a separate db.
How would I assign a different db to create the recordset rsIn?

Cheers,
Dean
 
In




dean.brunne@ said:
Hi- We have a third party software for forecasting called ForecastPro
that has an ODBC connection that outputs to our Access database. The
output format has the first field as an INDEX and the remaining fields
as the various product names. There are 20 tables in the same format
not all with the same products. They each have a total field.
Click to expand...
I need to create one table for all of this output that has the
following fields:
Click to expand...
Customer - name of the table (20 tables each represent different
customers)
Index - from the Index field. (straight forward)
Product - from the fieldnames of each table converted to a record
Baseline Units - the records in the product fields.
Click to expand...
I would like to create code to loop through the tables and insert into
one table in another database. I am unsure how to structure the
INSERT INTO sql and unsure how to do it inter database.
Click to expand...
Any suggestions would be appreciated.
Click to expand...

Here's some untested, recordset-looping "air code" to process a single
table whose name is passed to the function:

'----- start of "air code" -----
Sub NormalizeProductTable(TableName As String)

' add your own error-handling, of course

Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim fld As DAO.Field

Dim varIndex As Variant

With CurrentDb
Set rsIn = .OpenRecordset(TableName)
' Use your output table name in the following:
Set rsOut = .OpenRecordset("ProductUnits")
End With

With rsIn
Do Until .EOF
varIndex = !INDEX
For Each fld in .Fields
If fld.Name <> "INDEX" _
And fld.Name <> "Total" _
Then
rsOut.AddNew
rsOut!Customer = TableName
rsOut!Index = !INDEX
rsOut!Product = fld.Name
rsOut![Baseline Units] = fld.Value
rsOut.Update
End If
Next fld
.MoveNext
Loop
.Close
End With

rsOut.Close

Set rsIn = Nothing
Set rsOut = Nothing

End Sub
'----- end of "air code" -----

If I understood you correctly, that ought to be something along the
lines of what you need. You'd just need to call it for each of the
twenty tables, passing the table name each time.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Dirk,

Thanks, I wasn't expecting specific code. I probably misused the term
inter database as it is from one database to another. The 20 tables
are in a standalone db and I want to use this code in a separate db.
How would I assign a different db to create the recordset rsIn?
Click to expand...

Rather than:

With CurrentDb
Set rsIn = .OpenRecordset(TableName)
' Use your output table name in the following:
Set rsOut = .OpenRecordset("ProductUnits")
End With

You could define a second database object, instantiate it to point to the
correct database, and use it instead:

Dim dbOther As DAO.Database

dbOther = OpenDatabase("C:\Folder\Other.mdb")
Set rsIn = dbOther.OpenRecordset(TableName)
Set rsOut = CurrentDb.OpenRecordset("ProductUnits")
 
In
dean.brunne@<address snipped> wrote:
Hi- We have a third party software for forecasting called ForecastPro
that has an ODBC connection that outputs to our Access database. The
output format has the first field as an INDEX and the remaining fields
as the various product names. There are 20 tables in the same format
not all with the same products. They each have a total field.
I need to create one table for all of this output that has the
following fields:
Customer - name of the table (20 tables each represent different
customers)
Index - from the Index field. (straight forward)
Product - from the fieldnames of each table converted to a record
Baseline Units - the records in the product fields.
I would like to create code to loop through the tables and insert into
one table in another database. I am unsure how to structure the
INSERT INTO sql and unsure how to do it inter database.
Any suggestions would be appreciated.
Here's some untested, recordset-looping "air code" to process a single
table whose name is passed to the function:
'----- start of "air code" -----
Sub NormalizeProductTable(TableName As String)
' add your own error-handling, of course
Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim fld As DAO.Field
Dim varIndex As Variant
With CurrentDb
Set rsIn = .OpenRecordset(TableName)
' Use your output table name in the following:
Set rsOut = .OpenRecordset("ProductUnits")
End With
With rsIn
Do Until .EOF
varIndex = !INDEX
For Each fld in .Fields
If fld.Name <> "INDEX" _
And fld.Name <> "Total" _
Then
rsOut.AddNew
rsOut!Customer = TableName
rsOut!Index = !INDEX
rsOut!Product = fld.Name
rsOut![Baseline Units] = fld.Value
rsOut.Update
End If
Next fld
.MoveNext
Loop
.Close
End With
rsOut.Close
Set rsIn = Nothing
Set rsOut = Nothing
End Sub
'----- end of "air code" -----
If I understood you correctly, that ought to be something along the
lines of what you need. You'd just need to call it for each of the
twenty tables, passing the table name each time.
Click to expand...
Click to expand...
Hi Dirk,
Click to expand...
Thanks, I wasn't expecting specific code. I probably misused the term
inter database as it is from one database to another. The 20 tables
are in a standalone db and I want to use this code in a separate db.
How would I assign a different db to create the recordset rsIn?
Click to expand...

Rather than:

With CurrentDb
Set rsIn = .OpenRecordset(TableName)
' Use your output table name in the following:
Set rsOut = .OpenRecordset("ProductUnits")
End With

You could define a second database object, instantiate it to point to the
correct database, and use it instead:

Dim dbOther As DAO.Database

dbOther = OpenDatabase("C:\Folder\Other.mdb")
Set rsIn = dbOther.OpenRecordset(TableName)
Set rsOut = CurrentDb.OpenRecordset("ProductUnits")
Click to expand...

Hi Guys,

The code works fine with one little exception I need your help with.
The Total field that is skipped in the loop (If fld.Name = "Total") is
actually variable in the name. Total does not appear in the name at
all. It is the name of the customer (which the table name is also
called). This field is always the second field in the recordset. To
bypass it should I put a counter in the loop and skip the number
associated? Or is there an easier way?

Cheers,

Dean
 
In
[...]
With rsIn
Do Until .EOF
varIndex = !INDEX
For Each fld in .Fields
If fld.Name <> "INDEX" _
And fld.Name <> "Total" _
Then
rsOut.AddNew
rsOut!Customer = TableName
rsOut!Index = !INDEX
rsOut!Product = fld.Name
rsOut![Baseline Units] = fld.Value
rsOut.Update
End If
Next fld
.MoveNext
Loop
.Close
End With
Click to expand...
Click to expand...
[...]
The code works fine with one little exception I need your help with.
The Total field that is skipped in the loop (If fld.Name = "Total") is
actually variable in the name. Total does not appear in the name at
all. It is the name of the customer (which the table name is also
called). This field is always the second field in the recordset. To
bypass it should I put a counter in the loop and skip the number
associated? Or is there an easier way?
Click to expand...

If I understand you right, you could do it by looping through the fields
numerically, like this:

'----- start of code example #1 -----
Dim I as Integer

With rsIn
Do Until .EOF
varIndex = !INDEX
For I = 0 To (.Fields.Count - 1)
' Skip the second field.
If I <> 1 Then
With .Fields(I)
If .Name <> "INDEX" _
Then
rsOut.AddNew
rsOut!Customer = TableName
rsOut!Index = varIndex
rsOut!Product = .Name
rsOut![Baseline Units] = .Value
rsOut.Update
End If
End With
End If
Next I
.MoveNext
Loop
.Close
End With

'----- end of code example #1 -----

Alternatively, if the field name you want to skip is the same as the
variable TableName, as you seem to imply, then you can just modify your
existing code:

'----- start of code example #2 -----
With rsIn
Do Until .EOF
varIndex = !INDEX
For Each fld in .Fields
If fld.Name <> "INDEX" _
And fld.Name <> TableName _
Then
rsOut.AddNew
rsOut!Customer = TableName
rsOut!Index = !INDEX
rsOut!Product = fld.Name
rsOut![Baseline Units] = fld.Value
rsOut.Update
End If
Next fld
.MoveNext
Loop
.Close
End With

'----- end of code example #2 -----

There's another way, too, using the field's OrdinalPosition property to
distinguish it, but one of the above two approaches is probably
simplest.
 
Back
Top