adding a record from excel to access 1

[newprogamer]

Hello Everyone,

I am using VBA. I have created several excel spreadsheets. I would like to write the data from cells in excel to access database. I am using the code below. I keep getting a compile error. The program stops executing at the "Dim db as Database" statement. The error is "User-defined type not defined". Can anyone tell me what I need to do. Thanks!

NewProgrammer


'write to database

Dim db As Database
Dim Rs As Recordset

Set db = OpenDatabase("C:\Quotes\quote.mdb")

' open the database
Set Rs = db.OpenRecordset("FileName", dbOpenTable)

' Add a new record into the database

With Rs
.AddNew ' create a new record
' add values to each field in the record
Debug.Print strInitials
.Fields("QuotedBy") = strInitials
Debug.Print strDate
.Fields("Date") = strDate

End With

 

[Crookshanks]

Hello

I guess you try to run this code in excel. Before you can automate (talk to other applications) you need to set
references in your VBA-Editor. Now 'database' is not recognized as an object.

Hope this helps a bit.

 

[xlbo]

That would be under
Tools>References
in the VBE
You need to tick the MS Access reference

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[newprogamer]

Thanks guys,

I have set a reference to Tools - Reference - MS Access 11.0 Object Library". But, the database object still does not appear when I "Dim db As Database".

Any help is appreciated.

 

[xlbo]

the database object still does not appear"

The database wopn't open by DIMming it

DIMming is for DEFINING variables. Once it is defined, you then need to open and set a variable to it...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[newprogamer]

Thanks, I thought my code above was dimming, opening, and setting the variabes. Please let me know what you think the problem is.Thanks

 

[pbrodsky]

Actually, you probably want to reference either a 'Microsoft ActiveX Data Objects' or 'Microsoft DAO' library.

 

[kphu]

This is the code I just tested and it works with no issues. You'll have to adjust the values for your specific needs.

Some info for your reference while you read the code.

I created a database called db1.mdb
Within Db1.mdb is a table named Table1.
Fields within Table1 is "a", "b", "c", "d"


In references of excel you'll need to make sure "Microsoft DAO 3.6 Object Library" is checked.

Sub intodb()
Dim db As Database
Dim Rs As Recordset

Set db = OpenDatabase("C:\Documents and Settings\Ken\My Documents\db1.mdb")

' open the database
Set Rs = db.OpenRecordset("Table1")

' Add a new record into the database

With Rs
    .AddNew ' create a new record
    ' add values to each field in the record
    Rs("a") = Range("A1").Value
    Rs("b") = Range("A2").Value
    Rs("c") = Range("A3").Value
    Rs("d") = Range("A4").Value
    .Update
End With

End Sub

 

[newprogamer]

Thanks pbrodsky and kphu so much!
The code I wrote above was able to execute after setting a reference to Microsoft DAO 3.6 Object Library. However, it did not write to the database . . . because I didn't have the ".update".

kphu,
Thanks for your help. Your code worked like a charm.

Thanks again,
New Programmer