Faster export from excel to access

[Guest]

Hi, I am developing a tool to generate forecasts. I use MS Access 97 to store
the forecast data, but for a variety of reasons I use MS Excel 2000 as the
user interface.

My problem is the time it takes to load a forecast. A data set for a
forecast will have around 5000 - 7000 rows, and I am currently exporting this
to access on a row by row basis using and VBA to create an SQL statement and
copying the row to a recordset. An example of the code I use is set out below:

strSQL = "INSERT INTO [WIP Forecast In] ( Channel, Campaign, Unit, State,
Version, Period, Type, uKey, Custs, Strategy, FinYr, Manager, AcceptMthd,
LoadedBy, LoadDate ) "
strSQL = strSQL & "SELECT """ & ch & """ AS Channel, """ & Camp
& """ AS Campaign, """ & Unit & """ AS Unit, """ & state & """ AS State, """
& Vers & """ AS Version, """ & Per & """ AS Per, """ & Typ & """ AS Type, """
& Key & """ AS uKey, """ & Custs & """ AS Custs, """ & Strat & """ AS
Strategy, """ & Fyr & """ AS FinYr, """ & mgr & """ AS Manager, """ & AccMthd
& """ AS AcceptMthd, """ & Usr & """ AS LoadedBy, """ & DatTim & """ AS
LoadDate;"

rst.Open strSQL, cnt

Is there any way that I can load the full set of data in one go? I have
tried unsuccessfully to create a VBA sub that would copy and then paste
append the data into the correct table.

Any help would be greatly appreciated.

 

[onedaywhen]

My problem is the time it takes to load a forecast. A data set for a
forecast will have around 5000 - 7000 rows, and I am currently exporting this
to access on a row by row basis using and VBA to create an SQL statement and
copying the row to a recordset. An example of the code I use is set out below:

strSQL = "INSERT INTO [WIP Forecast In] ( Channel, Campaign, Unit, State,
Version, Period, Type, uKey, Custs, Strategy, FinYr, Manager, AcceptMthd,
LoadedBy, LoadDate ) "
strSQL = strSQL & "SELECT """ & ch & """ AS Channel, """ & Camp
& """ AS Campaign, """ & Unit & """ AS Unit, """ & state & """ AS State, """
& Vers & """ AS Version, """ & Per & """ AS Per, """ & Typ & """ AS Type, """
& Key & """ AS uKey, """ & Custs & """ AS Custs, """ & Strat & """ AS

Strategy, """ & Fyr & """ AS FinYr, """ & mgr & """ AS Manager, """ & AccMthd
& """ AS AcceptMthd, """ & Usr & """ AS LoadedBy, """ & DatTim & """ AS
LoadDate;"

rst.Open strSQL, cnt

Is there any way that I can load the full set of data in one go?

Yes, using a ... well, using INSERT INTO..SELECT. Instead of building a
one row table on the fly in your select clause, as you have done, you
can SELECT the data from the worksheet and do it all in one hit e.g.
something like:

INSERT INTO [WIP Forecast In] ( Channel, Campaign, Unit, State,
Version, Period, Type, uKey, Custs, Strategy, FinYr, Manager,
AcceptMthd,
LoadedBy, LoadDate )
SELECT Channel, Campaign, Unit, State,
Version, Period, Type, uKey, Custs, Strategy, FinYr, Manager,
AcceptMthd,
LoadedBy, LoadDate FROM [Excel
8.0;HDR=Yes;Database=C:\MyWorkbook;].[MySheet$];

The above assumes you have column headers in the workbook that match
those in the database table.

Jamie.

--