Not sure this should be Excel or ACCESS

[heavenlyhost1]

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?

 

[Mike]

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

 

[marwan.hefnawy]

This code is put into Excel to pull data from an access dataabse.
What is exactly your question

 

[heavenlyhost1]

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.

 

[heavenlyhost1]

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.

 

[Mike]

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]

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?

 

[heavenlyhost1]

Oh, and the report in Excel is "Scorecard".

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.

 

[Mike]

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]

Did you want me to send you anything else? Thank you so much for your time
and attention to this.

 

[Mike]

If you can send my a copy of the database and the excel sheet it would help

 

[heavenlyhost1]

I don't see where I attach files or an email for you. How do I do this?

 

[heavenlyhost1]

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

 

[davyboom]

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

 

[Patrick Molloy]

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

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

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?

 

[davyboom]

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

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

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

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?

 

[Patrick Molloy]

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

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

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

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


:

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?