Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Data to a database
Does anyone have example code of adding records in a database?
When I try to record a macro of the addition of database records within MS Query, I don't get the desired results in a VBA Module. I only get the query string. thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Data to a database
Add a sub procedure to a new module and paste in the following. For this to
work you'll need an Access database named 'contacts.mdb' in a directory named 'C:\Data\Access' The database will need one table named contacts with two text fields; one named 'first' another named 'last' In your procedure you'll probably need to set a reference to ADO so Tools|References and check the box for 'Microsoft ActiveX Data Objects 2.x Library' ---------------------------------- Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim strConnect As String, strSQL As String strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Access\contacts.mdb;Persist Security Info=False" strSQL = "SELECT contacts.first, contacts.last " _ & "FROM contacts; " Set cnn = New ADODB.Connection cnn.Open strConnect Set rs = New ADODB.Recordset rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic rs.AddNew rs!first = "Dave" rs!last = "Patrick" rs.Update rs.Close cnn.Close ---------------------------------- (watch for line wrapping) -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Simon Shaw" wrote: | Does anyone have example code of adding records in a database? | | When I try to record a macro of the addition of database records within MS | Query, I don't get the desired results in a VBA Module. I only get the query | string. | | thanks | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Data to a database
Simpler:
Dim cnn As Object Set cnn = CreateObject("ADODB.Connection") cnn.Open _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Data\Access\contacts.mdb;" & _ "Persist Security Info=False" cnn.Execute _ "INSERT INTO Contacts (first,last) VALUES ('Dave','Patrick')" Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Write Macro to Fix Data Alignment (Data dump from Crystal to Excel | Excel Discussion (Misc queries) | |||
Tool bar: Data/Import external data/New database query | Setting up and Configuration of Excel | |||
database query not showing foxpro database How I import data | New Users to Excel | |||
is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
write data to access | Excel Programming |