Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel to Access Question
I have a question about exporting data from excel to access using ado.
i am kind of n00b still, teaching myself vba and vb, but i know C/C++/opengl/direct3d/java/html/perl/cgi/sql, etc. so i am not a programming n00b :D well, now for the question. i got sick of searching the help files and net trying to find a way to change a record using vba and ado. i use ado to open the database to add fields, so i am assuming there is a command to search out a key and edit and/or replace/delete/change the record. can someone inform me on a good way to do this? i only need to change one record at a time. thanks!! Nonlinear ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel to Access Question
The way to do this is use an ADO Command object to send an INSERT, UPDATE, or DELETE SQL statement to the database. Here's a quick example based on one of my databases. Note that this code requires you to set a reference to the Microsoft ActiveX Data Objects 2.X Object Library under Tools/References in the VBE. Sub UpdateAccessRecord() Dim cnn As ADODB.Connection Dim szConnection As String Dim szSQL As String szConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=E:\MyDatabase.mdb;" szSQL = "UPDATE Styles SET JobID = 999 WHERE StyleID = 37;" Set cnn = New ADODB.Connection cnn.Open szConnection cnn.Execute szSQL cnn.Close Set cnn = Nothing End Sub -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals http://www.appspro.com/ * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Nonlinear" wrote in message ... I have a question about exporting data from excel to access using ado. i am kind of n00b still, teaching myself vba and vb, but i know C/C++/opengl/direct3d/java/html/perl/cgi/sql, etc. so i am not a programming n00b :D well, now for the question. i got sick of searching the help files and net trying to find a way to change a record using vba and ado. i use ado to open the database to add fields, so i am assuming there is a command to search out a key and edit and/or replace/delete/change the record. can someone inform me on a good way to do this? i only need to change one record at a time. thanks!! Nonlinear ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access to Excel Question | Excel Discussion (Misc queries) | |||
Excel Pivot Table or Access Cross Tab Query Question | Excel Discussion (Misc queries) | |||
Question about limiting access to functions in Excel | Excel Discussion (Misc queries) | |||
Macro Question - Excel to Access and back again | Excel Discussion (Misc queries) | |||
Access Form In An Access Report (SubForm) Question | Links and Linking in Excel |