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.
Bookmarks