Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
I have a report in ACCESS where the user enters a start date and end date of
the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
This should get you started
Private Sub getField() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim rowNumber As Long 'C:\PathToYourMdb\Ilsa.mdb (Change) strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PathToYourMdb\DatabaseName.mdb;Persist Security Info=False" 'sSQL = "Replace with your query" sSQL = "SELECT Field1 From TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic rowNumber = 2 'Starting Row Number for data Do While (Not rs.EOF) Range("A" & rowNumber) = rs.Fields("Field1").Value rowNumber = rowNumber + 1 rs.MoveNext Loop rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing End Sub "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
This code is put into Excel to pull data from an access dataabse.
What is exactly your question |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
Thank you for your time in doing this. I am unfamiliar with sql, if this is
what your formula is, so do I copy this (changing the names as indicated) into an event somwhere in my ACCESS report (the DAte Submitted count is in the report)? Do I copy the whole thing there? As you can tell, I really require a ton of help, so if you don't have the time for this, I understand. "Mike" wrote: This should get you started Private Sub getField() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim rowNumber As Long 'C:\PathToYourMdb\Ilsa.mdb (Change) strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PathToYourMdb\DatabaseName.mdb;Persist Security Info=False" 'sSQL = "Replace with your query" sSQL = "SELECT Field1 From TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic rowNumber = 2 'Starting Row Number for data Do While (Not rs.EOF) Range("A" & rowNumber) = rs.Fields("Field1").Value rowNumber = rowNumber + 1 rs.MoveNext Loop rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing End Sub "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
An exact question is going to be difficult since the code is way over my
head. To simplify, I want to plug the "Total Submitted" from an ACCESS report data field into an EXCEL report, but ideally, I would like the EXCEL report to automatically refresh with the new total when the EXCEL report is opened. The Total Submitted contains a Count(*) of all applications submitted within a certain timeframe. So the user would have to run the ACCESS report first to get the total, then open the EXCEL report wherein EXCEL will automatically "grab" the total obtained by the ACCESS report. Clear as mud? Maybe I'm way over my head with this. I almost got the result with the link feature, but couldn't get just the total to appear on the EXCEL report. " wrote: This code is put into Excel to pull data from an access dataabse. What is exactly your question |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
Your correct in saying change the names as indicated. You need to put this
into a excel module. This would be all you need. You would not need the Access report. If you could give me the your databasename, the name of the field I could get it so you could work with it. "heavenlyhost1" wrote: Thank you for your time in doing this. I am unfamiliar with sql, if this is what your formula is, so do I copy this (changing the names as indicated) into an event somwhere in my ACCESS report (the DAte Submitted count is in the report)? Do I copy the whole thing there? As you can tell, I really require a ton of help, so if you don't have the time for this, I understand. "Mike" wrote: This should get you started Private Sub getField() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim rowNumber As Long 'C:\PathToYourMdb\Ilsa.mdb (Change) strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PathToYourMdb\DatabaseName.mdb;Persist Security Info=False" 'sSQL = "Replace with your query" sSQL = "SELECT Field1 From TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic rowNumber = 2 'Starting Row Number for data Do While (Not rs.EOF) Range("A" & rowNumber) = rs.Fields("Field1").Value rowNumber = rowNumber + 1 rs.MoveNext Loop rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing End Sub "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
Thank you Mike. The ACCESS database name is RedCross-Volunteers, the Report
name in ACCESS is "Percentage of Tracking Returned (by date)", and the field in that report is "Total Submitted". By excel module, is that a macro? "Mike" wrote: Your correct in saying change the names as indicated. You need to put this into a excel module. This would be all you need. You would not need the Access report. If you could give me the your databasename, the name of the field I could get it so you could work with it. "heavenlyhost1" wrote: Thank you for your time in doing this. I am unfamiliar with sql, if this is what your formula is, so do I copy this (changing the names as indicated) into an event somwhere in my ACCESS report (the DAte Submitted count is in the report)? Do I copy the whole thing there? As you can tell, I really require a ton of help, so if you don't have the time for this, I understand. "Mike" wrote: This should get you started Private Sub getField() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim rowNumber As Long 'C:\PathToYourMdb\Ilsa.mdb (Change) strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PathToYourMdb\DatabaseName.mdb;Persist Security Info=False" 'sSQL = "Replace with your query" sSQL = "SELECT Field1 From TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic rowNumber = 2 'Starting Row Number for data Do While (Not rs.EOF) Range("A" & rowNumber) = rs.Fields("Field1").Value rowNumber = rowNumber + 1 rs.MoveNext Loop rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing End Sub "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
Oh, and the report in Excel is "Scorecard".
"Mike" wrote: Your correct in saying change the names as indicated. You need to put this into a excel module. This would be all you need. You would not need the Access report. If you could give me the your databasename, the name of the field I could get it so you could work with it. "heavenlyhost1" wrote: Thank you for your time in doing this. I am unfamiliar with sql, if this is what your formula is, so do I copy this (changing the names as indicated) into an event somwhere in my ACCESS report (the DAte Submitted count is in the report)? Do I copy the whole thing there? As you can tell, I really require a ton of help, so if you don't have the time for this, I understand. "Mike" wrote: This should get you started Private Sub getField() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim rowNumber As Long 'C:\PathToYourMdb\Ilsa.mdb (Change) strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PathToYourMdb\DatabaseName.mdb;Persist Security Info=False" 'sSQL = "Replace with your query" sSQL = "SELECT Field1 From TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic rowNumber = 2 'Starting Row Number for data Do While (Not rs.EOF) Range("A" & rowNumber) = rs.Fields("Field1").Value rowNumber = rowNumber + 1 rs.MoveNext Loop rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing End Sub "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
Yes your right again about the module in excel being a Macro.
As far as the report in access in concerned you dont need it. "heavenlyhost1" wrote: Thank you Mike. The ACCESS database name is RedCross-Volunteers, the Report name in ACCESS is "Percentage of Tracking Returned (by date)", and the field in that report is "Total Submitted". By excel module, is that a macro? "Mike" wrote: Your correct in saying change the names as indicated. You need to put this into a excel module. This would be all you need. You would not need the Access report. If you could give me the your databasename, the name of the field I could get it so you could work with it. "heavenlyhost1" wrote: Thank you for your time in doing this. I am unfamiliar with sql, if this is what your formula is, so do I copy this (changing the names as indicated) into an event somwhere in my ACCESS report (the DAte Submitted count is in the report)? Do I copy the whole thing there? As you can tell, I really require a ton of help, so if you don't have the time for this, I understand. "Mike" wrote: This should get you started Private Sub getField() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim rowNumber As Long 'C:\PathToYourMdb\Ilsa.mdb (Change) strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PathToYourMdb\DatabaseName.mdb;Persist Security Info=False" 'sSQL = "Replace with your query" sSQL = "SELECT Field1 From TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic rowNumber = 2 'Starting Row Number for data Do While (Not rs.EOF) Range("A" & rowNumber) = rs.Fields("Field1").Value rowNumber = rowNumber + 1 rs.MoveNext Loop rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing End Sub "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
Did you want me to send you anything else? Thank you so much for your time
and attention to this. "Mike" wrote: Yes your right again about the module in excel being a Macro. As far as the report in access in concerned you dont need it. "heavenlyhost1" wrote: Thank you Mike. The ACCESS database name is RedCross-Volunteers, the Report name in ACCESS is "Percentage of Tracking Returned (by date)", and the field in that report is "Total Submitted". By excel module, is that a macro? "Mike" wrote: Your correct in saying change the names as indicated. You need to put this into a excel module. This would be all you need. You would not need the Access report. If you could give me the your databasename, the name of the field I could get it so you could work with it. "heavenlyhost1" wrote: Thank you for your time in doing this. I am unfamiliar with sql, if this is what your formula is, so do I copy this (changing the names as indicated) into an event somwhere in my ACCESS report (the DAte Submitted count is in the report)? Do I copy the whole thing there? As you can tell, I really require a ton of help, so if you don't have the time for this, I understand. "Mike" wrote: This should get you started Private Sub getField() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim rowNumber As Long 'C:\PathToYourMdb\Ilsa.mdb (Change) strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PathToYourMdb\DatabaseName.mdb;Persist Security Info=False" 'sSQL = "Replace with your query" sSQL = "SELECT Field1 From TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic rowNumber = 2 'Starting Row Number for data Do While (Not rs.EOF) Range("A" & rowNumber) = rs.Fields("Field1").Value rowNumber = rowNumber + 1 rs.MoveNext Loop rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing End Sub "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
If you can send my a copy of the database and the excel sheet it would help
"heavenlyhost1" wrote: Did you want me to send you anything else? Thank you so much for your time and attention to this. "Mike" wrote: Yes your right again about the module in excel being a Macro. As far as the report in access in concerned you dont need it. "heavenlyhost1" wrote: Thank you Mike. The ACCESS database name is RedCross-Volunteers, the Report name in ACCESS is "Percentage of Tracking Returned (by date)", and the field in that report is "Total Submitted". By excel module, is that a macro? "Mike" wrote: Your correct in saying change the names as indicated. You need to put this into a excel module. This would be all you need. You would not need the Access report. If you could give me the your databasename, the name of the field I could get it so you could work with it. "heavenlyhost1" wrote: Thank you for your time in doing this. I am unfamiliar with sql, if this is what your formula is, so do I copy this (changing the names as indicated) into an event somwhere in my ACCESS report (the DAte Submitted count is in the report)? Do I copy the whole thing there? As you can tell, I really require a ton of help, so if you don't have the time for this, I understand. "Mike" wrote: This should get you started Private Sub getField() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim rowNumber As Long 'C:\PathToYourMdb\Ilsa.mdb (Change) strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PathToYourMdb\DatabaseName.mdb;Persist Security Info=False" 'sSQL = "Replace with your query" sSQL = "SELECT Field1 From TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic rowNumber = 2 'Starting Row Number for data Do While (Not rs.EOF) Range("A" & rowNumber) = rs.Fields("Field1").Value rowNumber = rowNumber + 1 rs.MoveNext Loop rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing End Sub "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
I don't see where I attach files or an email for you. How do I do this?
"Mike" wrote: If you can send my a copy of the database and the excel sheet it would help "heavenlyhost1" wrote: Did you want me to send you anything else? Thank you so much for your time and attention to this. "Mike" wrote: Yes your right again about the module in excel being a Macro. As far as the report in access in concerned you dont need it. "heavenlyhost1" wrote: Thank you Mike. The ACCESS database name is RedCross-Volunteers, the Report name in ACCESS is "Percentage of Tracking Returned (by date)", and the field in that report is "Total Submitted". By excel module, is that a macro? "Mike" wrote: Your correct in saying change the names as indicated. You need to put this into a excel module. This would be all you need. You would not need the Access report. If you could give me the your databasename, the name of the field I could get it so you could work with it. "heavenlyhost1" wrote: Thank you for your time in doing this. I am unfamiliar with sql, if this is what your formula is, so do I copy this (changing the names as indicated) into an event somwhere in my ACCESS report (the DAte Submitted count is in the report)? Do I copy the whole thing there? As you can tell, I really require a ton of help, so if you don't have the time for this, I understand. "Mike" wrote: This should get you started Private Sub getField() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim rowNumber As Long 'C:\PathToYourMdb\Ilsa.mdb (Change) strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PathToYourMdb\DatabaseName.mdb;Persist Security Info=False" 'sSQL = "Replace with your query" sSQL = "SELECT Field1 From TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic rowNumber = 2 'Starting Row Number for data Do While (Not rs.EOF) Range("A" & rowNumber) = rs.Fields("Field1").Value rowNumber = rowNumber + 1 rs.MoveNext Loop rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing End Sub "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
Mike, here's what I tried to do so far. I think maybe I should take out the
quotes on a few items? Of course, it doesn't work because I have no idea how to do this. Help!?! Private Sub getField() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim rowNumber As Long 'J:\Staff Services\Volunteer Services\RedCross-Volunteers.ldb strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data" Source=J:\Staff Services\Volunteer Services\RedCross-Volunteers.ldb;Persist Security Info=False" 'sSQL = "Percentage of Tracking Returned (by date submitted)" sSQL = "SELECT Date Submitted From RedCross-Volunteers.ldb" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic rowNumber = 2 'Starting Row Number for data Do While (Not rs.EOF) Range("A" & rowNumber) = rs.Fields("Date Submitted").Value rowNumber = rowNumber + 1 "Mike" wrote: If you can send my a copy of the database and the excel sheet it would help "heavenlyhost1" wrote: Did you want me to send you anything else? Thank you so much for your time and attention to this. "Mike" wrote: Yes your right again about the module in excel being a Macro. As far as the report in access in concerned you dont need it. "heavenlyhost1" wrote: Thank you Mike. The ACCESS database name is RedCross-Volunteers, the Report name in ACCESS is "Percentage of Tracking Returned (by date)", and the field in that report is "Total Submitted". By excel module, is that a macro? "Mike" wrote: Your correct in saying change the names as indicated. You need to put this into a excel module. This would be all you need. You would not need the Access report. If you could give me the your databasename, the name of the field I could get it so you could work with it. "heavenlyhost1" wrote: Thank you for your time in doing this. I am unfamiliar with sql, if this is what your formula is, so do I copy this (changing the names as indicated) into an event somwhere in my ACCESS report (the DAte Submitted count is in the report)? Do I copy the whole thing there? As you can tell, I really require a ton of help, so if you don't have the time for this, I understand. "Mike" wrote: This should get you started Private Sub getField() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim rowNumber As Long 'C:\PathToYourMdb\Ilsa.mdb (Change) strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PathToYourMdb\DatabaseName.mdb;Persist Security Info=False" 'sSQL = "Replace with your query" sSQL = "SELECT Field1 From TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic rowNumber = 2 'Starting Row Number for data Do While (Not rs.EOF) Range("A" & rowNumber) = rs.Fields("Field1").Value rowNumber = rowNumber + 1 rs.MoveNext Loop rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing End Sub "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
HI all,
I am learning to use SQL w. Excel. I copied the follwowing book example into VBA(Excel) code window, I got problem with Jet.OLEDB.4.0 Book example code: Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _ sqlstr As String, dbFile As String, usernm As String, pWord As String) Set dbcon = New ADODB.Connection dbcon.Open "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" & dbFile & ";", _ usernm, pWord Set dbrs = New ADODB.Recordset dbrs.Open sqlstr, dbcon End Sub Question: MY VBA module debug got into error on this line "Provider=Microsoft.jet.OLEDB.4.0: Data Source="....... Error Message [run time error '-2147467259 (80004005)' Automation error, unspecified error] what has been worng? how to fix? thanks, davy "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
Sub LoadDataFromAccess()
Dim MyFile As String 'DB name Dim con As New ADODB.Connection Dim rst As New ADODB.Recordset Dim SQL As String Dim i As Long 'for/next loop counter MyFile = "Risk.mdb" SQL = "SELECT * FROM BondTable" con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile rst.Open SQL, con, adOpenStatic Cells.Clear 'add headings For i = 0 To rst.Fields.Count - 1 Cells(1, i + 1).Value = rst.Fields(i).Name Next Range("A2").CopyFromRecordset rst "davyboom" wrote in message ... HI all, I am learning to use SQL w. Excel. I copied the follwowing book example into VBA(Excel) code window, I got problem with Jet.OLEDB.4.0 Book example code: Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _ sqlstr As String, dbFile As String, usernm As String, pWord As String) Set dbcon = New ADODB.Connection dbcon.Open "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" & dbFile & ";", _ usernm, pWord Set dbrs = New ADODB.Recordset dbrs.Open sqlstr, dbcon End Sub Question: MY VBA module debug got into error on this line "Provider=Microsoft.jet.OLEDB.4.0: Data Source="....... Error Message [run time error '-2147467259 (80004005)' Automation error, unspecified error] what has been worng? how to fix? thanks, davy "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
Hi Patrcik,
thought your answer is to me. I hd copy yr. module into my Excel VBA editor nand changed the poitner to my mdb file and its table. Simialr error echoed. Just wonder hw u reference yr. module. I got "ticked" in my reference- Microsoft ADO (multidimensional) 2.8 Microsoft ADO ext 2.8 for DDL and security Microsoft ADO2.8 library Microsoft Jet & application objects 2.6 library Microsoft ADO recordset 2.8 Microsoft OLEDB provider for OLAP services connection dialog 8.0 I really do not know which give function to Jet.OLEDB.4.0 can anyonehelp me to resolve? thanks, davy "Patrick Molloy" wrote: Sub LoadDataFromAccess() Dim MyFile As String 'DB name Dim con As New ADODB.Connection Dim rst As New ADODB.Recordset Dim SQL As String Dim i As Long 'for/next loop counter MyFile = "Risk.mdb" SQL = "SELECT * FROM BondTable" con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile rst.Open SQL, con, adOpenStatic Cells.Clear 'add headings For i = 0 To rst.Fields.Count - 1 Cells(1, i + 1).Value = rst.Fields(i).Name Next Range("A2").CopyFromRecordset rst "davyboom" wrote in message ... HI all, I am learning to use SQL w. Excel. I copied the follwowing book example into VBA(Excel) code window, I got problem with Jet.OLEDB.4.0 Book example code: Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _ sqlstr As String, dbFile As String, usernm As String, pWord As String) Set dbcon = New ADODB.Connection dbcon.Open "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" & dbFile & ";", _ usernm, pWord Set dbrs = New ADODB.Recordset dbrs.Open sqlstr, dbcon End Sub Question: MY VBA module debug got into error on this line "Provider=Microsoft.jet.OLEDB.4.0: Data Source="....... Error Message [run time error '-2147467259 (80004005)' Automation error, unspecified error] what has been worng? how to fix? thanks, davy "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not sure this should be Excel or ACCESS
only reference required is
Microsoft ActiveX Data Objects 2.7 Library the Connection string is in this, and mine should look like this: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Risk.mdb" The provider tells the connection that its going to connect to an Access database "davyboom" wrote in message ... Hi Patrcik, thought your answer is to me. I hd copy yr. module into my Excel VBA editor nand changed the poitner to my mdb file and its table. Simialr error echoed. Just wonder hw u reference yr. module. I got "ticked" in my reference- Microsoft ADO (multidimensional) 2.8 Microsoft ADO ext 2.8 for DDL and security Microsoft ADO2.8 library Microsoft Jet & application objects 2.6 library Microsoft ADO recordset 2.8 Microsoft OLEDB provider for OLAP services connection dialog 8.0 I really do not know which give function to Jet.OLEDB.4.0 can anyonehelp me to resolve? thanks, davy "Patrick Molloy" wrote: Sub LoadDataFromAccess() Dim MyFile As String 'DB name Dim con As New ADODB.Connection Dim rst As New ADODB.Recordset Dim SQL As String Dim i As Long 'for/next loop counter MyFile = "Risk.mdb" SQL = "SELECT * FROM BondTable" con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile rst.Open SQL, con, adOpenStatic Cells.Clear 'add headings For i = 0 To rst.Fields.Count - 1 Cells(1, i + 1).Value = rst.Fields(i).Name Next Range("A2").CopyFromRecordset rst "davyboom" wrote in message ... HI all, I am learning to use SQL w. Excel. I copied the follwowing book example into VBA(Excel) code window, I got problem with Jet.OLEDB.4.0 Book example code: Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _ sqlstr As String, dbFile As String, usernm As String, pWord As String) Set dbcon = New ADODB.Connection dbcon.Open "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" & dbFile & ";", _ usernm, pWord Set dbrs = New ADODB.Recordset dbrs.Open sqlstr, dbcon End Sub Question: MY VBA module debug got into error on this line "Provider=Microsoft.jet.OLEDB.4.0: Data Source="....... Error Message [run time error '-2147467259 (80004005)' Automation error, unspecified error] what has been worng? how to fix? thanks, davy "heavenlyhost1" wrote: I have a report in ACCESS where the user enters a start date and end date of the 'Date Submitted' field in the table. The report then gives the total count of records that that are generated between two given dates. I have an excel report where I would like this total count to go to when the excel report is opened. I've tried the ODBC thing and link thing but can't seem to get this one field to plug into the excel spreadsheet. Can this be done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trust Access to Visual Basic Project - Access to Excel and back | Excel Programming | |||
How do I access the access data via Excel 2002 with auto update ? | Excel Programming | |||
Importing data from Access to Excel, but I need to vary the table from Access | Excel Programming | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
Access data -work in Excel- save in Access | Excel Programming |