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

[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

 

[Dirk Goldgar]

In

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.

 

[dean.brunne]

In

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.

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

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

- Show quoted text -

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

 

[Douglas J. Steele]

In

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.

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

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

- Show quoted text -

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?

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")

 

[dean.brunne]

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.

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?

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")

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

 

[Dirk Goldgar]

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

[...]
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?

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.