running access

Hi,
I am trying to run a macro, I am using the right code db.Execute "Macro1"
???

Thank you all


Dim db As Database, rs As Recordset, r As Long
Dim cncpath As String
cncpath = "N:\1CNCACCESSAPPS\AccessApps\exceltemp\transf.mdb "

Set db = OpenDatabase(cncpath)
' open the database
Set rs = db.OpenRecordset("maindb", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("tcid") = Sheets("Data").Range("R2").Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop

'-------------------------------------------------------------------------
r = 3 ' the start row in the worksheet
Do While Len(Range("I" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("tcid") = Sheets("Data").Range("R2").Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop

rs.Close
Set rs = Nothing
db.Execute "Macro1"
db.Close
Set db = Nothing

'Run Macro on MS Access
RunCodeFromAccess

'End Code
Me.Hide

The Execute method is for running SQL statements.

I believe you need:

Dim appAccess As Access.Application

Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase cncpath
appAccess.DoCmd.RunMacro "Macro1"
appAccess.CloseCurrentDatabase
Set appAccess = Nothing

Not sure how to integrate that with your existing code, because your
existing code looks suspicious to me. Access doesn't have a Range object nor
a Sheets collection in it, so I'm not sure whether you're trying to run this
from Excel or Access!

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Bre-x" wrote in message
...
Hi,
I am trying to run a macro, I am using the right code db.Execute "Macro1"
???

Thank you all


Dim db As Database, rs As Recordset, r As Long
Dim cncpath As String
cncpath = "N:\1CNCACCESSAPPS\AccessApps\exceltemp\transf.mdb "

Set db = OpenDatabase(cncpath)
' open the database
Set rs = db.OpenRecordset("maindb", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("tcid") = Sheets("Data").Range("R2").Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop

'-------------------------------------------------------------------------
r = 3 ' the start row in the worksheet
Do While Len(Range("I" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("tcid") = Sheets("Data").Range("R2").Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop

rs.Close
Set rs = Nothing
db.Execute "Macro1"
db.Close
Set db = Nothing

'Run Macro on MS Access
RunCodeFromAccess

'End Code
Me.Hide