excel and access
 

Hi,

I am looking for a VBA way of connecting to aces via excel.
I want to click a button and data appears depending on what input I give
from and inputbox eg all data from table one that starts with name=fred.

My other thread on this was closed.

excel and access
 

Hi at

The following site has some details and code snippets of common tasks in
excel/access

http://www.excelkb.com/?cNode=1I7C3V&pNodes=8M3M1M

Other Sites that I find usefull all the time
http://www.cpearson.com/excel/topic.aspx
http://www.j-walk.com/
http://www.mcgimpsey.com/index.html
http://www.mvps.org/dmcritchie/excel/excel.htm
http://www.rondebruin.nl/

HTH



"at" wrote:

Hi,

I am looking for a VBA way of connecting to aces via excel.
I want to click a button and data appears depending on what input I give
from and inputbox eg all data from table one that starts with name=fred.

My other thread on this was closed.

excel and access
 

I found this code but I get an error when writing data to excel

Dim DBFullName As String
Dim TableName As String
Dim TargetRange As Range

DBFullName = "F:\accsess_ecel\db1.mdb"
TableName = "Table1"
Set TargetRange = Range("c1")

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
' open the recordset
.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable

' RS2WS rs, TargetRange ' write data from the recordset to the
worksheet
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value =
rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs ' error here

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

"steve_doc" wrote:

Hi at

The following site has some details and code snippets of common tasks in
excel/access

http://www.excelkb.com/?cNode=1I7C3V&pNodes=8M3M1M

Other Sites that I find usefull all the time
http://www.cpearson.com/excel/topic.aspx
http://www.j-walk.com/
http://www.mcgimpsey.com/index.html
http://www.mvps.org/dmcritchie/excel/excel.htm
http://www.rondebruin.nl/

HTH



"at" wrote:

Hi,

I am looking for a VBA way of connecting to aces via excel.
I want to click a button and data appears depending on what input I give
from and inputbox eg all data from table one that starts with name=fred.

My other thread on this was closed.

excel and access
 

i found this but get an error

Dim DBFullName As String
Dim TableName As String
Dim TargetRange As Range

DBFullName = "F:\accsess_ecel\db1.mdb"
TableName = "Table1"
Set TargetRange = Range("c1")

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
' open the recordset
.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable

' RS2WS rs, TargetRange ' write data from the recordset to the
worksheet
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value =
rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs 'error

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing


"steve_doc" wrote:

Hi at

The following site has some details and code snippets of common tasks in
excel/access

http://www.excelkb.com/?cNode=1I7C3V&pNodes=8M3M1M

Other Sites that I find usefull all the time
http://www.cpearson.com/excel/topic.aspx
http://www.j-walk.com/
http://www.mcgimpsey.com/index.html
http://www.mvps.org/dmcritchie/excel/excel.htm
http://www.rondebruin.nl/

HTH



"at" wrote:

Hi,

I am looking for a VBA way of connecting to aces via excel.
I want to click a button and data appears depending on what input I give
from and inputbox eg all data from table one that starts with name=fred.

My other thread on this was closed.

excel and access
 

What error message or number, and if you press the [Debug] button, what line
of the code is hightlighted? Is it the line you have a 'error comment at the
end of?

"at" wrote:

i found this but get an error

Dim DBFullName As String
Dim TableName As String
Dim TargetRange As Range

DBFullName = "F:\accsess_ecel\db1.mdb"
TableName = "Table1"
Set TargetRange = Range("c1")

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
' open the recordset
.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable

' RS2WS rs, TargetRange ' write data from the recordset to the
worksheet
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value =
rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs 'error

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing


"steve_doc" wrote:

Hi at

The following site has some details and code snippets of common tasks in
excel/access

http://www.excelkb.com/?cNode=1I7C3V&pNodes=8M3M1M

Other Sites that I find usefull all the time
http://www.cpearson.com/excel/topic.aspx
http://www.j-walk.com/
http://www.mcgimpsey.com/index.html
http://www.mvps.org/dmcritchie/excel/excel.htm
http://www.rondebruin.nl/

HTH



"at" wrote:

Hi,

I am looking for a VBA way of connecting to aces via excel.
I want to click a button and data appears depending on what input I give
from and inputbox eg all data from table one that starts with name=fred.

My other thread on this was closed.