Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Query Using Access Database Hangs on 2nd Execute
I have a MS Access 2003 Database named "AS400 Fields.mdb". This
database contains links to tables on an AS400. In MS Excel 2003, I have VBA code that creates and executes queries using the Access database, and returns the results to an Excel sheet. There are many of these Excel query files and they all work at the same time on different users' machines. The first time the query is executed, results are returned to Excel in usually less than 10 seconds. However, if the query is executed a second time (without closing the Excel workbook), the code hangs at the line surrounded by "=",s in the code below (this was tested with message boxes). If the Excel file containing the VBA code is closed and re-opened the code executes normally. Is there a problem in my code? Am I doing something wrong? Thanks, Mike Private Sub Execute_Query() Dim X As Integer Dim DB1 As ADODB.Connection Dim RS1 As ADODB.Recordset Const ConnectionStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source= Myserver Location Goes here\AS400 Fields.mdb" Set DB1 = New ADODB.Connection DB1.Open ConnectionStr Set RS1 = New ADODB.Recordset '============================= RS1.Open QryStr, DB1 '============================= 'Copy the data ActiveCell.Offset(1, 0).Select ActiveCell.CopyFromRecordset RS1 RS1.Close DB1.Close Set RS1 = Nothing Set DB1 = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Query Using Access Database Hangs on 2nd Execute
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Query Using Access Database Hangs on 2nd Execute
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Query Using Access Database Hangs on 2nd Execute
(Jamie Collins) wrote in message . com...
(Mike Knight) wrote ... I will experiment with leaving the connection open. Something else to try might be to execute multiple times in a VBA module the .mdb when open in the MS Access UI, using the CurrentProject.Connection object (which AFAIK is an 'always connected' connection). I'm wondering if Excel is disconnecting to the .mdb but the .mdb connection to the AS400 remains open and locked in some way. Jamie. -- Jamie, Are you saying that I should try opening the Access file from Access before executing a query? The AS400 connection does not seem to be the problem. We simultaneously executed the same Excel query (opened read only) on two different machines. One of the machines would not execute the query a second time, but the other one would. The one that would, at other times, would not. Since both Excels were using the same Access database, I don't believe the AS400 was locked. I wonder if this is a "memory leak" problem - somewhat similar to the Microsoft article 319998 concerning Excel sheets? I modified my code to create a new instance of Excel as per article 246335, but the problem still occurs. If I manually close the instance of Excel that ran the VBA, manually open another instance, and re-run the VBA, no problem seems to occur. The reason I use Access is as follows. In Excel 97, DAO3.5, I could connect directly to the AS400 and use VBA functions to manipulate and "clean-up" AS400 data (padded spaces, convert decimals, etc.) for use in Excel. After upgrading to Excel 2003, DAO3.6, the ability to connect directly and use the functions seemed to go away for the most part. Also, I could never get satisfactory results connecting with ADO. Using ADO and Access 2003 as a go-between restored complete use of VBA functions. Basically, Excel VBA writes the SQL for an Access query. Mike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Query Using Access Database Hangs on 2nd Execute
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Query Using Access Database Hangs on 2nd Execute
Another thought: perhaps some of the table's properties that relate to
the link could be refreshed e.g. How To Use ADO to Refresh/Create Linked Table for Password Secured Jet 4.0 Database: http://support.microsoft.com/default...22&Product=ado Microsoft Jet 4.0 Provider Properties: http://msdn.microsoft.com/library/de...properties.asp Jamie. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Query Using Access Database Hangs on 2nd Execute
"TK" wrote in message ...
Hi Mike: Let me add a couple more suggestions I put some error handling in your procedure and a connection test. The procedure did not fail as tested on this computer but perhaps a procedure on a different computer may have a glitch and the error handler should fail it.. Is there a need to declare the connection string as Const. It could cause problems if declared out of the procedure. One other consideration I have found is if none of your procedures calls the Access repair and compact utility the DB can get huge and slow.. If you are not familiar with the utility its Access Tools/DataBase Utilites/ Compact and Repair. If you check the file size in explorer before and after you see if it was necessary. Private Sub Execute_Query() On Error GoTo ErrHandler Dim X As Integer Dim DB1 As ADODB.Connection Dim RS1 As ADODB.Recordset 'Is there a need to declare this as a Const if it 'gets declared outside of the procedure you have form scope Const ConnectionStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source= Myserver Location Goes here\AS400 Fields.mdb" 'I prefer Dim Connections As String ConnectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source= Myserver Location Goes here\AS400 Fields.mdb" Set DB1 = New ADODB.Connection DB1.Open ConnectionStr 'Test to see if we are connected If DB1.State = adStateOpen Then MsgBox "Welcome to! " Else MsgBox "No Data today." End If Set RS1 = New ADODB.Recordset '============================= RS1.Open strSQL, DB1 '============================= 'Copy the data ActiveCell.Offset(1, 0).Select ActiveCell.CopyFromRecordset RS1 RS1.Close DB1.Close 'Test to see if we are still connected If DB1.State = adStateOpen Then MsgBox "Welcome to! " Else MsgBox "Where Closed!" End If Set RS1 = Nothing Set DB1 = Nothing Exit Sub ErrHandler: MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly End Sub Good Luck TK TK, Sorry I'm so late in responding - accounting period end activities got in the way! First, the database is compacted and has remained at 16,488KB - no data is actually stored from Excel queries in the Access database - only linked tables to the AS400. Second, I removed the code declaring the connection string as a constant, DIMed the variable, and set it equal to the provider + data source. Third, I added the Msgbox statements and the error checking. Fourth, the file was saved on the server and Excel closed. I have two computers side by side, both connected to the server. On computer 1, I opened Excel and the file, ran the changed code and the messages were displayed: "Welcome to" and "Where Closed" (after data was returned). Without closing Excel or the file, I re-ran the program and got the message "Welcome to". No data or error was returned after an appropriate time - the hour-glass was displayed. I did not stop the code (this can't be done with control break), but left Excel and the file "running". On computer 2, I opened read-only the same file as on computer 1 from the server location (only after computer1 hung-up). I ran the code and received the same results as were received the first time the code was run on computer 1, within seconds. The code on computer 2 was executed again and the computer "hung-up". Note that both computers were executing the same code from the same server file, and that both computers were using the same Access Database on the server. It doesn't appear that an AS400 connection problem within Access exists - computer 2 was able to use the DB even while Computer 1 was using it. Both computers are Intel Pentium 4 2.66GH 248MB Windows XP SP1 Office 2003. Thanks for your input - any suggestions are appreciated. Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel query to access database | Excel Discussion (Misc queries) | |||
Database query to import from Access to Excel | Excel Discussion (Misc queries) | |||
Excel 2000 hangs on new database query XP | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
How to do an Access Database Query in Excel | Excel Programming |