| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running SQL queries for Access using Excel VBA
I have code using OLE DB to go into Access and retrieve results using SQL
but it runs for forever.....and never stops. Can someone advise what is going wrong. I run the same structure on a different Access DB (using a different Select statement) and it works fine. It hangs when it trying to Open the recordset after the SQL code.Here is the code: Dim DBFullName As String Dim Cnct As String, Src As String Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset DBFullName = ThisWorkbook.Path & "\Supply.mdb" Set cnn = New ADODB.Connection Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; " Cnct = Cnct & "Data Source=" & DBFullName & ";" cnn.Open ConnectionString:=Cnct 'Set recordset as ADODB recordset Set rs = New ADODB.Recordset With Recordset 'Filter Src = "SELECT [All Supply].[Resource ID], [All Supply].[New Resource Group], [All Supply].[New Resource Type]," Src = Src & " [All Supply].[Employee Status], [All Supply].[Employee ID], [All Supply].[Teamplay Name], " Src = Src & "[All Supply].[Cont / FTE], [All Supply].[Tech Mgr ID], [All Supply].[Other Notes]," Src = Src & " [Detailed to High Level - Groups].[High Level Group], [Detailed to High Level - Groups].[Higher Level Group], " Src = Src & " [Detailed to High Level - Types].[High Level Type]" Src = Src & " FROM [All Supply], [Detailed to High Level - Groups],[Detailed to High Level - Types]" Src = Src & "WHERE [All Supply].[New Resource Group] Not Like 'Unknown' AND [All Supply].[Employee Status]='Active' AND " Src = Src & "[All Supply].[Employee ID] Not Like '01*'" Src = Src & " GROUP BY [All Supply].[Resource ID], [All Supply].[New Resource Group], [All Supply].[New Resource Type], " Src = Src & "[All Supply].[Employee Status], [All Supply].[Employee ID], [All Supply].[Teamplay Name], " Src = Src & "[All Supply].[Cont / FTE], [All Supply].[Tech Mgr ID], [All Supply].[Other Notes], " Src = Src & "[Detailed to High Level - Groups].[High Level Group], [Detailed to High Level - Groups].[Higher Level Group], " Src = Src & "[Detailed to High Level - Types].[High Level Type] " Src = Src & "ORDER by [All Supply].[New Resource Group],[All Supply].[New Resource Type]" rs.Open Source:=Src, ActiveConnection:=cnn (IT HANGS HERE) ThisWorkbook.Sheets("Data").Columns("A:L").ClearCo ntents For col = 0 To rs.Fields.Count - 1 ThisWorkbook.Sheets("Data").Range("A1").Offset(0, col).Value = _ rs.Fields(col).Name Next ThisWorkbook.Worksheets("Data").Range("A2").CopyFr omRecordset rs End With Set rs = Nothing ' Close the connection. cnn.Close Set cnn = Nothing |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Running SQL queries for Access using Excel VBA
nevermind. I found my own problem
"ibeetb" wrote in message ... I have code using OLE DB to go into Access and retrieve results using SQL but it runs for forever.....and never stops. Can someone advise what is going wrong. I run the same structure on a different Access DB (using a different Select statement) and it works fine. It hangs when it trying to Open the recordset after the SQL code.Here is the code: Dim DBFullName As String Dim Cnct As String, Src As String Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset DBFullName = ThisWorkbook.Path & "\Supply.mdb" Set cnn = New ADODB.Connection Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; " Cnct = Cnct & "Data Source=" & DBFullName & ";" cnn.Open ConnectionString:=Cnct 'Set recordset as ADODB recordset Set rs = New ADODB.Recordset With Recordset 'Filter Src = "SELECT [All Supply].[Resource ID], [All Supply].[New Resource Group], [All Supply].[New Resource Type]," Src = Src & " [All Supply].[Employee Status], [All Supply].[Employee ID], [All Supply].[Teamplay Name], " Src = Src & "[All Supply].[Cont / FTE], [All Supply].[Tech Mgr ID], [All Supply].[Other Notes]," Src = Src & " [Detailed to High Level - Groups].[High Level Group], [Detailed to High Level - Groups].[Higher Level Group], " Src = Src & " [Detailed to High Level - Types].[High Level Type]" Src = Src & " FROM [All Supply], [Detailed to High Level - Groups],[Detailed to High Level - Types]" Src = Src & "WHERE [All Supply].[New Resource Group] Not Like 'Unknown' AND [All Supply].[Employee Status]='Active' AND " Src = Src & "[All Supply].[Employee ID] Not Like '01*'" Src = Src & " GROUP BY [All Supply].[Resource ID], [All Supply].[New Resource Group], [All Supply].[New Resource Type], " Src = Src & "[All Supply].[Employee Status], [All Supply].[Employee ID], [All Supply].[Teamplay Name], " Src = Src & "[All Supply].[Cont / FTE], [All Supply].[Tech Mgr ID], [All Supply].[Other Notes], " Src = Src & "[Detailed to High Level - Groups].[High Level Group], [Detailed to High Level - Groups].[Higher Level Group], " Src = Src & "[Detailed to High Level - Types].[High Level Type] " Src = Src & "ORDER by [All Supply].[New Resource Group],[All Supply].[New Resource Type]" rs.Open Source:=Src, ActiveConnection:=cnn (IT HANGS HERE) ThisWorkbook.Sheets("Data").Columns("A:L").ClearCo ntents For col = 0 To rs.Fields.Count - 1 ThisWorkbook.Sheets("Data").Range("A1").Offset(0, col).Value = _ rs.Fields(col).Name Next ThisWorkbook.Worksheets("Data").Range("A2").CopyFr omRecordset rs End With Set rs = Nothing ' Close the connection. cnn.Close Set cnn = Nothing |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Running SQL queries for Access using Excel VBA
have you tried setting the recordset to the recordset property of a pivot
table? You might find this a fair bit easier than mangling code. Also, you might find creating a query in the mdb with parameters faster and easier too. "ibeetb" wrote: I have code using OLE DB to go into Access and retrieve results using SQL but it runs for forever.....and never stops. Can someone advise what is going wrong. I run the same structure on a different Access DB (using a different Select statement) and it works fine. It hangs when it trying to Open the recordset after the SQL code.Here is the code: Dim DBFullName As String Dim Cnct As String, Src As String Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset DBFullName = ThisWorkbook.Path & "\Supply.mdb" Set cnn = New ADODB.Connection Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; " Cnct = Cnct & "Data Source=" & DBFullName & ";" cnn.Open ConnectionString:=Cnct 'Set recordset as ADODB recordset Set rs = New ADODB.Recordset With Recordset 'Filter Src = "SELECT [All Supply].[Resource ID], [All Supply].[New Resource Group], [All Supply].[New Resource Type]," Src = Src & " [All Supply].[Employee Status], [All Supply].[Employee ID], [All Supply].[Teamplay Name], " Src = Src & "[All Supply].[Cont / FTE], [All Supply].[Tech Mgr ID], [All Supply].[Other Notes]," Src = Src & " [Detailed to High Level - Groups].[High Level Group], [Detailed to High Level - Groups].[Higher Level Group], " Src = Src & " [Detailed to High Level - Types].[High Level Type]" Src = Src & " FROM [All Supply], [Detailed to High Level - Groups],[Detailed to High Level - Types]" Src = Src & "WHERE [All Supply].[New Resource Group] Not Like 'Unknown' AND [All Supply].[Employee Status]='Active' AND " Src = Src & "[All Supply].[Employee ID] Not Like '01*'" Src = Src & " GROUP BY [All Supply].[Resource ID], [All Supply].[New Resource Group], [All Supply].[New Resource Type], " Src = Src & "[All Supply].[Employee Status], [All Supply].[Employee ID], [All Supply].[Teamplay Name], " Src = Src & "[All Supply].[Cont / FTE], [All Supply].[Tech Mgr ID], [All Supply].[Other Notes], " Src = Src & "[Detailed to High Level - Groups].[High Level Group], [Detailed to High Level - Groups].[Higher Level Group], " Src = Src & "[Detailed to High Level - Types].[High Level Type] " Src = Src & "ORDER by [All Supply].[New Resource Group],[All Supply].[New Resource Type]" rs.Open Source:=Src, ActiveConnection:=cnn (IT HANGS HERE) ThisWorkbook.Sheets("Data").Columns("A:L").ClearCo ntents For col = 0 To rs.Fields.Count - 1 ThisWorkbook.Sheets("Data").Range("A1").Offset(0, col).Value = _ rs.Fields(col).Name Next ThisWorkbook.Worksheets("Data").Range("A2").CopyFr omRecordset rs End With Set rs = Nothing ' Close the connection. cnn.Close Set cnn = Nothing |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Open all Excel spreadsheets/fles before running queries | Excel Discussion (Misc queries) | |||
| Access Queries into Excel | Excel Worksheet Functions | |||
| Running multiple queries in Microsoft Query in Excel | Excel Programming | |||
| Running Queries to Oracle from Excel 2000 | Excel Programming | |||
| Running Access queries from Excel | Excel Programming | |||
Linear Mode
