How do you get data to/from Access DB in Excel VB?
 

Sub Macro1()
main_dir = ThisWorkbook.Path & "\"
Set myobj = CreateObject("Access.Application")
myobj.Visible = True
c_db = main_dir & "c_hist.mdb"
myobj.OpenCurrentDatabase (c_db)
myobj.docmd.openTable ("t1")

'What do you put in here to change the value(s) in record 1?

myobj.docmd.Close
myobj.docmd.openQuery ("member")

'What do you put in here to copy the values
'returned by the query to the clipboard?

myobj.docmd.Close
End Sub

How do you get data to/from Access DB in Excel VB?
 

After, myobj.OpenCurrentDatabase (c_db)
Delete the line :- myobj.docmd.openTable ("t1")
And add following code:-

Dim myRecSet

Set myRecSet = myobj.CurrentDb.OpenRecordset("t1")

myRecSet.MoveFirst 'It is now pointing to the first row
myRecSet.Edit 'Edit it
myRecSet.Fields(0) = "Whatever" 'change cell (1, 1)
myRecSet.Fields(1) = "Anything else" 'change cell (1 ,2)
'and so on.
myRecSet.Update 'Necessary to save changes made
myRecSet.MoveNext 'Point to the next row
myRecSet.Edit ' Again edit and change values
'same as above code to change recrods.

If you want add new record then
myRecSet.MoveLast 'not necesary as such
myRecSet.Addnew
myRecSet.Fields(0) = "Whatever
myRecSet.Fields(1)= "anything else"
'and so on till total columns
MyRecSet.Update

To read the values from the table again you need to do MoveFirst, then
MoveNext and so on.

Dim x1, x2 '....and so on
MyRecSet.MoveFirst
x1 = MyRecSet.Fields(0)
x2 = MyRecSet.Fields(1)
'and so on

Instead of using Reference to MS Access Libray you better work with
Microsoft ActiveX Data Object 2.8 Library
Then you need not open actual Access application. The user PC need not
even have MS Access installed.
Exaple of working with it (First add reference to above mentioned
activex library):
Dim connStr1 As String, connStr2 As String, _
dataConn As Connection, dataRec As Recordset

connStr1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
connStr2 = "Full path name of the MDB file"
dataConn.Open connStr1 & connStr2
dataRec.Open "SELECT * FROM t1", dataConn, adOpenKeyset, _
adLockOptimistic, adCmdText

So dataRec is now refferring to tabe t1 of your access file.
You can work with it same way as above (instead of myRecSet, use
dataRec).

The SELECT statement above is very versatile. Supposed in table t1 fist
colum name is COL1 then
You can to "SELECT * FROM t1 ORDER BY COL1 ASC"
or DESC in place of ASC. Returns the dataRec arranged in sorted by COL1
ascending or descending.
Or if COL1 is a date field and you want to select between two dates
"SELECT * FROM t1 WHERE COL1 BETWEEN #DATE1# AND #DATE2#".

See MSDN Help on SELECT Statement for further details.

Sharad




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!