Transferspreadsheet -> target back-end

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All:
I would like to import an Excel file as an Access table to a back end:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ~to a table in
the BackEnd~, fileName, True

But instead of importing it to the font-end, i wish to create the table in
the backend and then link this new table to the front-end.

I know i can create a table in the backend, then link the tables, and import
using the linked table, that has been done. BUT what happens when, in the
spread sheet:
1) the field names change
2) the field order change
3) the field data type change
4) the number of fields change

This create new table and import needs to be run only once per "deployment"
by a Access user with little or no time to "manually import the table"

I have been able to create an association table which eliminates problems
after the data has been imported.

(why do companies make great data collection software with no reporting
capabilities?)

thanks for your help
-rogge
 
Hi Rogge,

I'm not quite certain I understand your explanation. Do you want to
import the user's "once-per-deployment" spreadsheet data with its new
field names etc. into a new table in the back end, or do you want to get
the data into your existing table, with the new field names mapped to
the old, data types converted, etc.?

Also, do you need to expose this data as a linked table in the front
end's GUI, or do you just need to run queries against it?

I get the impression that most if not all you want to do can be achieved
by writing code that constructs and uses SQL statements. For instance,
something like this, executed in the front end, will create a table in
the back end with new field names and order, using data from a
worksheet:

SELECT
OldField3 AS NewField1,
OldField2 As NewField2,
OldField1 As NewField3
INTO NewTable IN 'C:\folder\sub folder\foo.mdb'
FROM [Excel 8.0;HDR=Yes;database=C:\bar;].[Sheet1$]
;

And this does much the same job as a linked table:

SELECT *
FROM NewTable IN 'C:\folder\sub folder\foo.mdb'
;
 
Thank you for the quick response.

Yes to your first answer: import into new table in back end. Will this keep
the data types (text, long, date, bolean)?

"SELECT * INTO NewTable IN '" & CurrnetPeojct.Path & "\Data\data.mdb'
FROM [Excel 8.0;HDR=Yes;database=C:\bar;].[Sheet1$];"

Then I'll link the table to the front end...

BUT, i just found out today that my table will now conain all the same
fields in the same order.

Thank you very much for your help.

John Nurick said:
Hi Rogge,

I'm not quite certain I understand your explanation. Do you want to
import the user's "once-per-deployment" spreadsheet data with its new
field names etc. into a new table in the back end, or do you want to get
the data into your existing table, with the new field names mapped to
the old, data types converted, etc.?

Also, do you need to expose this data as a linked table in the front
end's GUI, or do you just need to run queries against it?

I get the impression that most if not all you want to do can be achieved
by writing code that constructs and uses SQL statements. For instance,
something like this, executed in the front end, will create a table in
the back end with new field names and order, using data from a
worksheet:

SELECT
OldField3 AS NewField1,
OldField2 As NewField2,
OldField1 As NewField3
INTO NewTable IN 'C:\folder\sub folder\foo.mdb'
FROM [Excel 8.0;HDR=Yes;database=C:\bar;].[Sheet1$]
;

And this does much the same job as a linked table:

SELECT *
FROM NewTable IN 'C:\folder\sub folder\foo.mdb'
;




All:
I would like to import an Excel file as an Access table to a back end:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ~to a table in
the BackEnd~, fileName, True

But instead of importing it to the font-end, i wish to create the table in
the backend and then link this new table to the front-end.

I know i can create a table in the backend, then link the tables, and import
using the linked table, that has been done. BUT what happens when, in the
spread sheet:
1) the field names change
2) the field order change
3) the field data type change
4) the number of fields change

This create new table and import needs to be run only once per "deployment"
by a Access user with little or no time to "manually import the table"

I have been able to create an association table which eliminates problems
after the data has been imported.

(why do companies make great data collection software with no reporting
capabilities?)

thanks for your help
-rogge
Click to expand...
Click to expand...
 
Remember that Excel doesn't really have a concept of "data type": any
cell can contain any value, with formats superimposed. For more on this,
see the Access help topic "Initializing the Microsoft Excel Driver"
(under Microsoft Jet SQL Reference) and this article:
http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Since you now know the field names and types, the simplest thing is just
to create the back end table manually and then build INSERT (append)
queries instead of SELECT INTO ones.


Thank you for the quick response.

Yes to your first answer: import into new table in back end. Will this keep
the data types (text, long, date, bolean)?

"SELECT * INTO NewTable IN '" & CurrnetPeojct.Path & "\Data\data.mdb'
FROM [Excel 8.0;HDR=Yes;database=C:\bar;].[Sheet1$];"

Then I'll link the table to the front end...

BUT, i just found out today that my table will now conain all the same
fields in the same order.

Thank you very much for your help.

John Nurick said:
Hi Rogge,

I'm not quite certain I understand your explanation. Do you want to
import the user's "once-per-deployment" spreadsheet data with its new
field names etc. into a new table in the back end, or do you want to get
the data into your existing table, with the new field names mapped to
the old, data types converted, etc.?

Also, do you need to expose this data as a linked table in the front
end's GUI, or do you just need to run queries against it?

I get the impression that most if not all you want to do can be achieved
by writing code that constructs and uses SQL statements. For instance,
something like this, executed in the front end, will create a table in
the back end with new field names and order, using data from a
worksheet:

SELECT
OldField3 AS NewField1,
OldField2 As NewField2,
OldField1 As NewField3
INTO NewTable IN 'C:\folder\sub folder\foo.mdb'
FROM [Excel 8.0;HDR=Yes;database=C:\bar;].[Sheet1$]
;

And this does much the same job as a linked table:

SELECT *
FROM NewTable IN 'C:\folder\sub folder\foo.mdb'
;




All:
I would like to import an Excel file as an Access table to a back end:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ~to a table in
the BackEnd~, fileName, True

But instead of importing it to the font-end, i wish to create the table in
the backend and then link this new table to the front-end.

I know i can create a table in the backend, then link the tables, and import
using the linked table, that has been done. BUT what happens when, in the
spread sheet:
1) the field names change
2) the field order change
3) the field data type change
4) the number of fields change

This create new table and import needs to be run only once per "deployment"
by a Access user with little or no time to "manually import the table"

I have been able to create an association table which eliminates problems
after the data has been imported.

(why do companies make great data collection software with no reporting
capabilities?)

thanks for your help
-rogge
Click to expand...
Click to expand...
Click to expand...
 
Back
Top