Using Excel to retrieve Access Data Via SQL

[MSAccessRookie]

I have the need to have an Excel (2003) Spreadsheet open my Access (2003) database to locate data to display on the Spreadsheet. Below is a Function that was crafted from bits and pieces of several examples that I have found on the internet, and is intended to do just that.

The Function successfully completes Steps 1-3, and hangs up when it attempts to perform Step 4. Am I approaching this incorrectly?

[FONT=Courier New][FONT=Courier New][COLOR=gray][B]Option Explicit[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=black][B]----------------------------------------------------------------------[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B]Function GetIngredientByLot(LotNumber As String)[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B] 'Step 1: Declare the necessary variables[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B] Dim dbInfo As DAO.Database[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B] Dim rst As DAO.Recordset[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B] Dim strSQL As String[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B] 'Step 2: Identify the database and query[/B][/COLOR][/FONT]
 
 
[FONT=Courier New][FONT=Courier New][COLOR=blue][B] 'THIS OPENS THE CORRECT DATABASE[/B][/COLOR][/FONT]
 
[B][COLOR=green]  Set dbInfo = OpenDatabase("C:\Documents and Settings\user\Desktop\RDIFrontEnd.mdb")[/COLOR][/B][/FONT]
 
[FONT=Courier New][COLOR=green][B] 'Step 3: Build the SQL String which will become the recordset[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B] strSQL = "SELECT tblIngredients.Ingredient FROM tblRDINGRED " & _[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B]          "INNER JOIN tblIngredients ON tblRDINGRED.IngredientID = tblIngredients.IngredientID " & _[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B]          "WHERE tblRDINGRED.RD_Lot = '" & LotNumber & "'"[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=blue][B] 'THIS CREATES THE CORRECT SQL QUERY[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B] Debug.Print strSQL[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B] 'Stop[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B] 'Step 4: Open a snapshot (view only) type recordset based on strSQL above[/B][/COLOR][/FONT]
 
 
[FONT=Courier New][FONT=Courier New][COLOR=blue][B] 'THE FUNCTION HANGS UP AT THIS POINT [/B][/COLOR][/FONT]
 
[/FONT][FONT=Courier New][COLOR=red][B] Set rst = dbInfo.OpenRecordset(strSQL, dbOpenSnapshot)[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B] Debug.Print LotNumber[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B] Debug.Print rst("Ingredient")[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B] 'Stop[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B] 'Step 5: Return the ingredient name from the SQL call[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B] GetIngredientByLot = rst("Ingredient")[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B] 'Step 5: Clear the necessary variables[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B] Set dbInfo = Nothing[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B] Set rst = Nothing[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B] strSQL = ""[/B][/COLOR][/FONT]
 
[FONT=Courier New][COLOR=green][B]End Function[/B][/COLOR][/FONT]
[/FONT]

 

[chergh]

I've just tried it out and it seems to work fine for me. Is all the info on your desktop or are you just using access to link to tables on another database somewhere?

You could try adding doevents after you create your recordset as your firststep.

 

[DCrake]

If you copy the SQL into the buffer and use this to create a new query using the SQL pane is it syntactically correct and does it return the results you were expecting.

David

 

[MSAccessRookie]

I've just tried it out and it seems to work fine for me. Is all the info on your desktop or are you just using access to link to tables on another database somewhere?

You could try adding doevents after you create your recordset as your firststep.

I forgot to mention that the Access Database Links to an SQL Server Database. Is that the issue? If so, any suggestions would be appreciated.

 

[chergh]

It could well result in the query taking longer to execute, depending on size of the recordset and how good your network is, and if it takes to long to execute then the next line of code could cause an issue. How long does the query take if you execute it within access?

 

[MSAccessRookie]

If you copy the SQL into the buffer and use this to create a new query using the SQL pane is it syntactically correct and does it return the results you were expecting.

David

Indeed it does. That was the way that I verified the SQL to be correct. The error appears to be either an issue in contacting with/connecting to the database, or that Excel will not allow the recordset ot be opened and populated.

 

[MSAccessRookie]

It could well result in the query taking longer to execute, depending on size of the recordset and how good your network is, and if it takes to long to execute then the next line of code could cause an issue. How long does the query take if you execute it within access?

The result is immediate when I place the SQL Code in an SQL Buffer and execute it. My past experience with VB SQL is that is (for the most part) as fast as or even faster than normal access queries.

 

[chergh]

I've done several projects pulling info out of backend db's and into excel to produce on demand reports and never had the issue you are describing. Try a simpler query and see if you can get it working or try creating the query within access instead of within your procedure and see if that helps.

 

[MSAccessRookie]

I've done several projects pulling info out of backend db's and into excel to produce on demand reports and never had the issue you are describing. Try a simpler query and see if you can get it working or try creating the query within access instead of within your procedure and see if that helps.

I simplified the query to remove the Lot Number Information Table (RDINGRED), and select from the Ingredient Table only (see below). The same situation occurs. I have to think that since the function seems to work for you, that my Excel may have security issues that yours does not have.

• Is the definition of rst not working as I expect it should?
• Is there a missing VB Library Reference?
• Is there a Security issue that I am currently unaware of?
• Are there other issues to consider?

[SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][SIZE=2] tblIngredients[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Ingredient [/SIZE][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][SIZE=2] tblIngredients [/SIZE]
[SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2] tblIngredients[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]IngredientID [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'96'[/COLOR][/SIZE]

NOTE: I have added an attachment containing a Spreadhseet that has the VB Code along with some sample data. I hope this will help to better understand my issue and to find a solution.