How to connect excel to an access database 3

[Grieg]

HI,

I'm fairly new to excel and looking for some help.

I have a spreadsheet where I need to send data captured via a dialog box to a table in an Access database.

How can I connect to the database and append the data to the relevant table.

I've been trying the code suggested in the excel 'help' function however declaring an item as a Jet workspace just gives me an error stating 'user-defined type not defined'

Could anyone let me know where I'm going wrong or point me to a relavtn online tutorial?!

Many thanks,
Grieg

 

[Nikita6003]

Hiya,

Excel can't simply send data to Access. For that, you need a connection object. In the good old Office 97 days, DAO (or Data Access Object) was used most; nowadays, a different type is used (ADO)
Excel doesn't speak either ADO or DAO by default, so the first thing you need to is to seta reference to one of the two. This'll allow you to 'talk' to Access using the DAO or ADO language.
Go to the VBA Editor screen, choose TOOLS > REFERENCES< and for DAO activate the Microsoft DAO 2.5/3.5 Compatibility Library. This version was shipped with office 97; later versions use 3.6 (but I had a few mishaps with that version in VB6 so I tend to stick to DAO 3.5)

Now you can start creating the connection with Access:

Dim l_wksWorkSpace As DAO.Workspace
Dim l_dbsDatabase As DAO.Database
Dim l_rsRecordset As DAO.Recordset


'Set up Jet workspace
Set l_wksWorkSpace = DBEngine.CreateWorkspace("MyWorkSpace", "admin", "", dbUseJet)
'open database - substitue the name of your mdb
Set l_dbsDatabase = l_wksWorkSpace.OpenDatabase("Y:\Data\Access\LotusNotes.mdb")

'Now you've got the connection open, you can open a table & append your data
'Substitute the name of your table
Set l_rsRecordset = l_dbsDatabase.OpenRecordset("tblRecipient", dbOpenTable, dbOpenDynamic)
'start new record
l_rsRecordset.AddNew

'set values of fields
l_rsRecordset.Fields("Name") = "ME"
l_rsRecordset.Fields("FullName") = "MEMEME"
l_rsRecordset.Fields("Function") = "ME = great"
l_rsRecordset.Fields("Phone") = "836836"
'does the actual save
l_rsRecordset.Update

'Release the recordset & db
l_rsRecordset.Close
l_dbsDatabase.Close

'release objects. Not strictly necessary but neater
Set l_rsRecordset = Nothing
Set l_dbsDatabase = Nothing
Set l_wksWorkSpace = Nothing

HTH

Cheers
Nikki

 

[Grieg]

Hi Nikki,

Thanks again for your reply and help (I think you replied to an posting of mine from last year as well!).

I don't think I explained myself properly in my posting as the main area I was having the problem with was the connection string and setting the reference however
I managed to get around the problem using the following code
(I have just included this in the reply in case anyone else is looking through the posts for a solution) however I have tried your code as well which worked perfectly - thank you.

------------------------------------------------------
Public Function AppendToMIS()

Dim ConnObj As Object
Dim ConnectStr As String
Dim ActionRst As Object
Dim SQLcommand As String

ConnectStr = &quot;Provider = Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source = j:\shared\mortrevi\LSMIS.MDB;&quot; & _
&quot;Jet OLEDBatabase Password=Password; &quot;

Set ConnObj = CreateObject(&quot;ADODB.Connection&quot
ConnObj.Open ConnectStr
Set ActionRst = CreateObject(&quot;ADODB.Recordset&quot
SQLcommand = SQLprimer 'SQL COMMAND TO SEND TO DATABASE
'SQLprimer is a string defined
'in a procedure calling the routine

Set ActionRst = objConn.Execute(SQLcommand)

End Function


Once again, thanks for your assistance and I hope all is well in the Netherlands.

Grieg.

 

[Nikita6003]

Hiya Grieg,

thanks for the start - couldn't make out from the orig. post whether you needed DAO or ADO

Good connection code, btw ;-)

Cheers
Nikki

& NL = cold! and wet and evry soggy at the moment