Getting data from Access into Excel

[Pozzo]

I posted a couple of days ago and didn't get any answers. Anyway, th
problem has evolved somewhat since so I'm re-posting in the hope tha
somebody can help this time.

I have a query on an Access database which gives me product details an
is fairly large, approx. 16,000 records. I am working on a spreadshee
to forecast sales and would like to pull in information based on th
product code entered by the user. My idea initially was simply t
create a query that imported this data into a sheet and reference th
appropriate cells from the forecasting sheet. I quickly realised tha
all these references and lookups both made the workbook too slow an
physically too large. It seems obvious that the query needs to b
dynamically generated to only return the data relating to the produc
codes entered on the sheet. After perusing some of the posts here,
tried the Sql.request workbook function but unfortunately this seems t
execute the query in each cell rather than returning data to a range o
cells, and additionally refreshes every time the user moves cell (
need to keep automatic updates on).

Would it be possible using VBA to run the query, keep the returned dat
in an array, and then copy the data to the appropriate cell
programatically? If not, can anybody suggest a better solution.

edit: I am using Excel 2000, but the solution needs to work for '97

Any help is greatly appreciated.
Lee

 

[jeff]

Hi, Pozzo.

Here is some code I put together from Microsoft's
help on the web (knowledgebase). I take a value
in cell A2 (software build id #) and use it to
feed a dynamic query, putting the data starting
in row 5, each field into the next column. be sure to
watch the code for miswrapping
in this reply and sorry for the weird use of variables.

jeff

Sub ClientServerX2()

Dim XX As String
XX = Range("A2").Value

Dim dbsCurrent As Database
Dim qdfBestSellers As QueryDef
Dim qdfBonusEarners As QueryDef
Dim rstTopSeller As Recordset
Dim rstBonusRecipients As Recordset
Dim strAuthorList As String

' Open a database from which QueryDef objects can be
' created.
Set dbsCurrent = OpenDatabase("C:\Testing\TestDB.mdb")

' Create a temporary QueryDef object to retrieve
' data from a Microsoft SQL Server database.
Set qdfBestSellers = dbsCurrent.CreateQueryDef("")
With qdfBestSellers
.Connect = "ODBC;DATABASE=TestDB;UID=sa;PWD=;" & _
"DSN=TestDB"
.Sql = "SELECT recordid, build FROM
automationtimings " & _
"where build = '" & XX & "' ORDER BY build
DESC"
Set rstTopSeller = .OpenRecordset()
rstTopSeller.MoveFirst
End With

' Create a temporary QueryDef to retrieve data from
' a Microsoft SQL Server database based on the
results from
' the first query.
Set qdfBonusEarners = dbsCurrent.CreateQueryDef("")
With qdfBonusEarners
.Connect
= "ODBC;DATABASE=AutomationResults;UID=sa;PWD=;" & _
"DSN=AutomationResults"
.Sql = "SELECT * FROM automationtimings " & _
"WHERE build = '" & _
rstTopSeller!Build & "'"
Set rstBonusRecipients = .OpenRecordset()
End With

' Build the output string.
With rstBonusRecipients
r = 5
Do While Not .EOF
For j = 0 To 8
Cells(r, j + 1).Value =
rstBonusRecipients.Fields(j).Value

Next j
.MoveNext
r = r + 1
Loop
End With

rstTopSeller.Close
dbsCurrent.Close

End Sub

 

[Pozzo]

Thanks Jeff,

I am sure this is the solution I require. I've been playing aroun
with the code, but I'm not hugely experienced using VBA, and it won'
accept my connection string,

"DSN=MS Access Database;DBQ=C:\Documents and Settings\LH\M
Documents\Access Databases\Supply Chain - working"

even though this is the same one that was working for the SQL.reques
function. The message is: "Invalid string in pass-through query
(run-time 3305)

 

[onedaywhen]

Thanks Jeff,

I am sure this is the solution I require. I've been playing around
with the code, but I'm not hugely experienced using VBA, and it won't
accept my connection string...

The code is (indirectly) using MS Query. Before launching into a
code-based solution, try using the MS Query GUI tool (perhaps record a
macro while you do so). You'll find it under: Data, Import External
Data, New Database Query.

--