Visual Basic Code , VB.NET Code, VB Code
  Home   :  Code   :  Forums   :  Submit   :  Mailing List   :  About   :  Contact


Problems running VBA code within Excel


Problems running VBA code within Excel

Author
Message
sclward
sclward
Forum God
Forum God (1.2K reputation)Forum God (1.2K reputation)Forum God (1.2K reputation)Forum God (1.2K reputation)Forum God (1.2K reputation)Forum God (1.2K reputation)Forum God (1.2K reputation)Forum God (1.2K reputation)Forum God (1.2K reputation)

Group: Forum Members
Posts: 3, Visits: 14
Have created the following code to export Excel worksheet data to Access database. Had early issues with registering type to allow system to recognize the Dim statement. Setup object library to include Microsoft DAO 3.6 Object Library, but now when trying to compile macro I get a runtime error '429' that an ActiveX component can't create object on the Set db statement of the code. What am I missing?

Sub DAOFromExcelToAccess()
'exports data from the active worksheet to a table in an Access database
'this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\Documents and Settings\sward\My Documents\Austell Equipment\GM-130\Productivity Reports\Machine_Logging.mdb")
'open the database
Set rs = db.OpenRecordset("GM130", dbOpenTable)
'get all records in a table
r = 1 '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("Customer") = Range("A" & r).Value
.Fields("Metallizer") = Range("B" & r).Value
.Fields("Met_Date") = Range("C" & r).Value
.Fields("Vac_Roll_Number") = Range("D" & r).Value
.Fields("Met_Quality") = Range("E" & r).Value
.Fields("Met_Performance") = Range("F" & r).Value
.Fields("Met_Downtime") = Range("G" & r).Value
.Fields("Slitter") = Range("H" & r).Value
.Fields("Slit_Date") = Range("I" & r).Value
.Fields("Slit_Quality") = Range("J" & r).Value
.Fields("Slit_Performance") = Range("K" & r).Value
.Fields("Slit_Downtime") = Range("L" & r).Value
'add more fields if necessary...
.Update 'store the new record
End With
r = r + 1 'next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

GO


Similar Topics


Reading This Topic


Login
Existing Account
Email Address:


Password:


Social Logins

Select a Forum....

















A1VBCode Forums


Search