Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you use VBA to query an Access database without importing data
Hello,
I've developed an Access database that tracks service requests and when they are opened, closed, or if and when they are canceled. I would like to summarize this in an Excel chart such as number of requests opened and closed by month, but would rather not import the entire data table to perform this calculation. Is it possible to query the data table as it resides in Access so all I have to pull over is a few summary numbers? TIA Jon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you use VBA to query an Access database without importing data
Sure you can use VBA to do that, but why not just use Get External Data and
embed a query in your spread sheet. It is a lot easier than going the VBA route (unless you have good reason to do so). If you need to to go the VBA route reply back and I can give you some code. I am assuming that you know A little bit about Access... "JonR" wrote: Hello, I've developed an Access database that tracks service requests and when they are opened, closed, or if and when they are canceled. I would like to summarize this in an Excel chart such as number of requests opened and closed by month, but would rather not import the entire data table to perform this calculation. Is it possible to query the data table as it resides in Access so all I have to pull over is a few summary numbers? TIA Jon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you use VBA to query an Access database without importing
I'd like to see the code, if you would please.
At present there is no good reason to do it with VBA other than my own education, but I'm starting to get into data sets that are reaching the limits of Excel and may need to come up with alternatives to loading a sheet full of data. Thanks. "Jim Thomlinson" wrote: Sure you can use VBA to do that, but why not just use Get External Data and embed a query in your spread sheet. It is a lot easier than going the VBA route (unless you have good reason to do so). If you need to to go the VBA route reply back and I can give you some code. I am assuming that you know A little bit about Access... "JonR" wrote: Hello, I've developed an Access database that tracks service requests and when they are opened, closed, or if and when they are canceled. I would like to summarize this in an Excel chart such as number of requests opened and closed by month, but would rather not import the entire data table to perform this calculation. Is it possible to query the data table as it resides in Access so all I have to pull over is a few summary numbers? TIA Jon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you use VBA to query an Access database without importing
Jon,
The best way to accomplish that task is through ADO. You will also need to learn a little SQL to query the database. The nice thing about ADO is that it can be used to connect to any database that is an OLE DB provider, including Oracle and SQL server. Look at Microsoft Knowledge Base Article 246335 for an introduction. "JonR" wrote: I'd like to see the code, if you would please. At present there is no good reason to do it with VBA other than my own education, but I'm starting to get into data sets that are reaching the limits of Excel and may need to come up with alternatives to loading a sheet full of data. Thanks. "Jim Thomlinson" wrote: Sure you can use VBA to do that, but why not just use Get External Data and embed a query in your spread sheet. It is a lot easier than going the VBA route (unless you have good reason to do so). If you need to to go the VBA route reply back and I can give you some code. I am assuming that you know A little bit about Access... "JonR" wrote: Hello, I've developed an Access database that tracks service requests and when they are opened, closed, or if and when they are canceled. I would like to summarize this in an Excel chart such as number of requests opened and closed by month, but would rather not import the entire data table to perform this calculation. Is it possible to query the data table as it resides in Access so all I have to pull over is a few summary numbers? TIA Jon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you use VBA to query an Access database without importing
Here you are. This is a simple function for returning an ADO recordset based
on an SQL Query... private const m_cDBLocation as string = "C:\Mine.mdb" Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _ ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean) As ADODB.Recordset Dim strConnection As String On Error GoTo ErrorHandler strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & m_cDBLocation & ";" Set RunQuery = New ADODB.Recordset With RunQuery .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockBatchOptimistic End With RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " & strOrderBy, strConnection, , , adCmdText If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing Exit Function ErrorHandler: modErrors.HandleError m_cModule, "RunQuery" End Function Here is how you use the function... Dim rst As ADODB.Recordset Set rst = RunQuery(strSelect, strFrom, strWhere, strOrderBy, False) The function either returns a connected or disconnected recordset, depending on whether you want to update the recordset back to the database. To define the SQL statement write a query in access and then change the view to SQL and Tada... There it is. Fore something else cool try creating a pivot table and attaching it to the Access database. This also gets you around the pesky 65,535 limit of access and is a cool wat to summarize data, and even bring it straight into a chart. HTH "JonR" wrote: I'd like to see the code, if you would please. At present there is no good reason to do it with VBA other than my own education, but I'm starting to get into data sets that are reaching the limits of Excel and may need to come up with alternatives to loading a sheet full of data. Thanks. "Jim Thomlinson" wrote: Sure you can use VBA to do that, but why not just use Get External Data and embed a query in your spread sheet. It is a lot easier than going the VBA route (unless you have good reason to do so). If you need to to go the VBA route reply back and I can give you some code. I am assuming that you know A little bit about Access... "JonR" wrote: Hello, I've developed an Access database that tracks service requests and when they are opened, closed, or if and when they are canceled. I would like to summarize this in an Excel chart such as number of requests opened and closed by month, but would rather not import the entire data table to perform this calculation. Is it possible to query the data table as it resides in Access so all I have to pull over is a few summary numbers? TIA Jon |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you use VBA to query an Access database without importing
Sorry you need to strip out the error handler and the 65,535 limit is part of
Excel not Access. Too much egg nog... "Jim Thomlinson" wrote: Here you are. This is a simple function for returning an ADO recordset based on an SQL Query... private const m_cDBLocation as string = "C:\Mine.mdb" Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _ ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean) As ADODB.Recordset Dim strConnection As String On Error GoTo ErrorHandler strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & m_cDBLocation & ";" Set RunQuery = New ADODB.Recordset With RunQuery .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockBatchOptimistic End With RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " & strOrderBy, strConnection, , , adCmdText If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing Exit Function ErrorHandler: modErrors.HandleError m_cModule, "RunQuery" End Function Here is how you use the function... Dim rst As ADODB.Recordset Set rst = RunQuery(strSelect, strFrom, strWhere, strOrderBy, False) The function either returns a connected or disconnected recordset, depending on whether you want to update the recordset back to the database. To define the SQL statement write a query in access and then change the view to SQL and Tada... There it is. Fore something else cool try creating a pivot table and attaching it to the Access database. This also gets you around the pesky 65,535 limit of access and is a cool wat to summarize data, and even bring it straight into a chart. HTH "JonR" wrote: I'd like to see the code, if you would please. At present there is no good reason to do it with VBA other than my own education, but I'm starting to get into data sets that are reaching the limits of Excel and may need to come up with alternatives to loading a sheet full of data. Thanks. "Jim Thomlinson" wrote: Sure you can use VBA to do that, but why not just use Get External Data and embed a query in your spread sheet. It is a lot easier than going the VBA route (unless you have good reason to do so). If you need to to go the VBA route reply back and I can give you some code. I am assuming that you know A little bit about Access... "JonR" wrote: Hello, I've developed an Access database that tracks service requests and when they are opened, closed, or if and when they are canceled. I would like to summarize this in an Excel chart such as number of requests opened and closed by month, but would rather not import the entire data table to perform this calculation. Is it possible to query the data table as it resides in Access so all I have to pull over is a few summary numbers? TIA Jon |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you use VBA to query an Access database without importing
Hi Jon
If ADO is your choice I can help with some sample code If you still need some. What type of recordset are you importing that would be lager than Excel can import. I see post all the time that refer to Excels 65k limit. This is a row per column limit. Excels CopyFromRecordset can handle rss larger than 65k although it uses the end of column to terminate writing the rs you can direct it further. Jet uses the end of recordset (ie. EOF) so if you have a recordset with more than 65536 records (rows) direct Excel to write the next record to the column you want it in.. Example: You wish to bring a 3 field recordset with 80,000 records into Excel Worksheets("Sheet1").Range("A:A").CopyFromRecordse t Rs Worksheets("Sheet1").Range("D:D").CopyFromRecordse t Rs This will put the first 65+ records in column ABC and the rest in DEF Good Luck TK |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you use VBA to query an Access database without importing
You wish to bring a 3 field recordset with 80,000 records into Excel
Worksheets("Sheet1").Range("A:A").CopyFromRecordse t Rs Worksheets("Sheet1").Range("D:D").CopyFromRecordse t Rs A neat trick, however if you have less than 65K rows the second line will generate an EOF error, so an on error resume next could be useful. Jamie. -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you use VBA to query an Access database without importing
Hi Jamie
"onedaywhen" wrote: A neat trick, however if you have less than 65K rows the second line will generate an EOF error, so an on error resume next could be useful. Jamie. Now that you menshion it I would also expect the EOF error but in actual test it didn't happen, so I assume it is handled by CopyFromRecordset. Good Luck TK |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you use VBA to query an Access database without importing
TK wrote:
Now that you menshion it I would also expect the EOF error but in actual test it didn't happen, so I assume it is handled by CopyFromRecordset. It's cleverer than I thought <g. Jamie. -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you use VBA to query an Access database without importing
I have a similar question. The Get External Data works fine from my
computer, but is it possible for the embedded query to remain connected to Access, if the .xls is stored in a shared drive folder, and linked to by a hyperlink/URL from the corporate intranet page? As far as I can tell that removes the auto-refresh/link to the Access data, or am I missing something somewhere? Everyone is using Excel 2003, Access 2003 and do not have SharePoint where I work. They don't want to use DataAccess Pages. "Jim Thomlinson" wrote: Sure you can use VBA to do that, but why not just use Get External Data and embed a query in your spread sheet. It is a lot easier than going the VBA route (unless you have good reason to do so). If you need to to go the VBA route reply back and I can give you some code. I am assuming that you know A little bit about Access... "JonR" wrote: Hello, I've developed an Access database that tracks service requests and when they are opened, closed, or if and when they are canceled. I would like to summarize this in an Excel chart such as number of requests opened and closed by month, but would rather not import the entire data table to perform this calculation. Is it possible to query the data table as it resides in Access so all I have to pull over is a few summary numbers? TIA Jon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
importing/linking data from an Access 2003 Query to an Excel 2003 | Excel Discussion (Misc queries) | |||
Importing data via an external database query | Excel Discussion (Misc queries) | |||
Importing Data via Database Query | Excel Discussion (Misc queries) | |||
Importing Data from an Access Database Including a Hyperlink Colum | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |