Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel access data from Access?! | Excel Discussion (Misc queries) | |||
Data from Access to Excel | Excel Discussion (Misc queries) | |||
Access data -work in Excel- save in Access | Excel Programming | |||
Excel data to Access? | Excel Programming | |||
Data from Excel to Access | Excel Programming |