openning Access via Excel
 

hi all,

can you please correct my codes or any other setting for my Excel program?
I got this error "Complie error, User-defined type not defined", codes below
in Modules:

Public Sub iConn()
Set oaccess = New Access.Application
oaccess.OpenCurrentDatabase (Sheets("Main").Range("A1").Value)
Set db = oaccess.CurrentDb()
End Sub

openning Access via Excel
 

Check to see if you have set up a reference to Access.

In VBA, select Tools/References and ensure Access is checked (probably will
be listed as Microsoft Access).



"Huaqin" wrote:

hi all,

can you please correct my codes or any other setting for my Excel program?
I got this error "Complie error, User-defined type not defined", codes below
in Modules:

Public Sub iConn()
Set oaccess = New Access.Application
oaccess.OpenCurrentDatabase (Sheets("Main").Range("A1").Value)
Set db = oaccess.CurrentDb()
End Sub

openning Access via Excel
 

Thanks JMB, that worked. I have another error while the following codes,
"run-time error 424, object required", please help again if you have the
chance.

I try to input excel data into access db.....


Sub Add_Click()

Dim criteria As String
iConn
Set db = oaccess.CurrentDb()
Set rs = db.OpenRecordset("Customer_tbl", dbOpenDynaset)
If Cells(6, "C") < "" Then
criteria = "Customer_ID = '" & Cells(6, "C") & "'"
rs.FindFirst UCase(criteria)
If Not rs.NoMatch Then
MsgBox "Customer code is in the database already, please use
other."
rs.Close
db.Close
Exit Sub
Else
rs.AddNew
End If
rs!Customer_ID = UCase(Cells(6, "C"))
rs!Customer_Full_Name = UCase(Cells(7, "C"))
rs!Address = Cells(8, "C")
rs!City = Cells(9, "C")
rs!State = Cells(10, "C")
rs!Telephone = Cells(11, "C")
rs!Note = Cells(12, "C")
rs.Update
MsgBox "Record update."
Else
MsgBox "Please input Customer code and Full Name."
rs.Close
db.Close
Exit Sub
End If

rs.Close
db.Close

End Sub





"JMB" wrote:

Check to see if you have set up a reference to Access.

In VBA, select Tools/References and ensure Access is checked (probably will
be listed as Microsoft Access).



"Huaqin" wrote:

hi all,

can you please correct my codes or any other setting for my Excel program?
I got this error "Complie error, User-defined type not defined", codes below
in Modules:

Public Sub iConn()
Set oaccess = New Access.Application
oaccess.OpenCurrentDatabase (Sheets("Main").Range("A1").Value)
Set db = oaccess.CurrentDb()
End Sub

openning Access via Excel
 

Unfortunately, I have limited experience w/Access (I don't own Access - so
cannot set up a test to try to duplicate your problem).

I assume oaccess is declared as a public variable.

Does Excel highlight a specific line?


"Huaqin" wrote:

Thanks JMB, that worked. I have another error while the following codes,
"run-time error 424, object required", please help again if you have the
chance.

I try to input excel data into access db.....


Sub Add_Click()

Dim criteria As String
iConn
Set db = oaccess.CurrentDb()
Set rs = db.OpenRecordset("Customer_tbl", dbOpenDynaset)
If Cells(6, "C") < "" Then
criteria = "Customer_ID = '" & Cells(6, "C") & "'"
rs.FindFirst UCase(criteria)
If Not rs.NoMatch Then
MsgBox "Customer code is in the database already, please use
other."
rs.Close
db.Close
Exit Sub
Else
rs.AddNew
End If
rs!Customer_ID = UCase(Cells(6, "C"))
rs!Customer_Full_Name = UCase(Cells(7, "C"))
rs!Address = Cells(8, "C")
rs!City = Cells(9, "C")
rs!State = Cells(10, "C")
rs!Telephone = Cells(11, "C")
rs!Note = Cells(12, "C")
rs.Update
MsgBox "Record update."
Else
MsgBox "Please input Customer code and Full Name."
rs.Close
db.Close
Exit Sub
End If

rs.Close
db.Close

End Sub





"JMB" wrote:

Check to see if you have set up a reference to Access.

In VBA, select Tools/References and ensure Access is checked (probably will
be listed as Microsoft Access).



"Huaqin" wrote:

hi all,

can you please correct my codes or any other setting for my Excel program?
I got this error "Complie error, User-defined type not defined", codes below
in Modules:

Public Sub iConn()
Set oaccess = New Access.Application
oaccess.OpenCurrentDatabase (Sheets("Main").Range("A1").Value)
Set db = oaccess.CurrentDb()
End Sub