Excel to Access Question

  • Thread starter Thread starter Nonlinear
  • Start date Start date
N

Nonlinear

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
 
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 *
 
Back
Top