Web Development

General discussion


Excel to Access

By dennishanson ·
I am currently trying to programe an Excel spreadsheet to transfer info to an existing Access Data Base using the following VB code:-

Sub Add_Record_to_Contacts()
' Add_Record_to_Contacts Macro
' Macro recorded 04/07/2003 by Dennis R. Hanson


Dim DBS As Database
Dim RecSet As Recordset
Dim DBox As DialogSheet

' Open the database
dbname = "C:\Private\Contacts.mdb"
Set DBS = OpenDatabase(dbname)

Set RecSet = DBS.OpenRecordset(Name:="CV-Engineering", Type:=dbOpenDynaset)

With RecSet

.fields("Surname").Value = Sheets("CV-Info").Range("B1").Value
.fields("Middle Name").Value = Sheets("CV-Info").Range("B2").Value
.fields("Forname").Value = Sheets("CV-Info").Range("B3").Value.Update

End With

'Close the Data Base

End Sub

When I run the code I get an "Compile Error" meassage tell me "User-Defined Type not Defined"

Any help please.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel to Access

by tduval In reply to Excel to Access

You're using Access VBA with excel !

Neither database, recordset nor opendatabase are recognized by excel

Collapse -

Excel to Access

by sargasso In reply to Excel to Access


First, you will need to explicitly add the reference to the DAO library using the tools/references menu. Ensure you use the proper library and also ensure that the DAO library is in the reultant reference list above any reference to ADO. For example ( for OfficeXP ) the list might read:
o Visual Basic for Applications
o Microsoft Excel 10.0 Object Library
o OLE Automation
o Microsoft Office 10.0 Object Library
o Microsoft DAO 3.6 Object Library

If you have a reference to ANY ADO library then you must either reference objects explicitly, e.g. ADODB.Recordset and DAO.Recordset

Secondly, check if you have Option Explicit set in the declarations section of the module. If so then you will have to declare the variables you are using, like so:
Dim dbname as string

Its a GOOD idea to use option explicit, especially if you are in the learning phase, as its tooooo easy to create runtime object errors that you wont see in development with the normal user happiness degradation results.


Related Discussions

Related Forums