read Access table from VBA
I have an Access database c:\test\val.mdb
In this database I have a table "values". Values has the fields "label1" and "date1". date1 is unique. Now from Excel VBA I want to read label1 from this table with date1 as key. Can someone tell me how this is done? Please be specific (an example would be best) - also indicate which references I need to include in VBA to use Access functionality Ron |
read Access table from VBA
Ron,
You can use ADO Private Sub CommandButton1_Click() Set objConnection = CreateObject("ADODB.Connection") ' Set objrs = CreateObject("ADODB.Recordset") objConnection.Provider = "Microsoft.Jet.OLEDB.4.0" strSQL = "SELECT label1, date1 " strSQL = strSQL & "FROM values " objConnection.Open objrs.Open strSQL, objConnection x = 1 Do While Not objrs.EOF Range("A" & x) = objrs("label1") Range("B" & x) = objrs("date1") x = x + 1 Loop End Sub Mike Tomasura "Ron" wrote in message ... I have an Access database c:\test\val.mdb In this database I have a table "values". Values has the fields "label1" and "date1". date1 is unique. Now from Excel VBA I want to read label1 from this table with date1 as key. Can someone tell me how this is done? Please be specific (an example would be best) - also indicate which references I need to include in VBA to use Access functionality Ron |
read Access table from VBA
Mike, Thank You - I'll check it tomorrow!
I thought it would be something with CreateObject("Access.application") and linking to some Access DLL library to get the Access object model ... but if your way works - it is good enough for me! Ron -----Original Message----- Ron, You can use ADO Private Sub CommandButton1_Click() Set objConnection = CreateObject("ADODB.Connection") ' Set objrs = CreateObject("ADODB.Recordset") objConnection.Provider = "Microsoft.Jet.OLEDB.4.0" strSQL = "SELECT label1, date1 " strSQL = strSQL & "FROM values " objConnection.Open objrs.Open strSQL, objConnection x = 1 Do While Not objrs.EOF Range("A" & x) = objrs("label1") Range("B" & x) = objrs("date1") x = x + 1 Loop End Sub Mike Tomasura "Ron" wrote in message ... I have an Access database c:\test\val.mdb In this database I have a table "values". Values has the fields "label1" and "date1". date1 is unique. Now from Excel VBA I want to read label1 from this table with date1 as key. Can someone tell me how this is done? Please be specific (an example would be best) - also indicate which references I need to include in VBA to use Access functionality Ron . |
read Access table from VBA
Mike,
just tried it.... where do I make the link to my database c:\test\val.mdb ? Now the Objconnection.open fails - probably because I don't tell where to open. Ron -----Original Message----- Ron, You can use ADO Private Sub CommandButton1_Click() Set objConnection = CreateObject("ADODB.Connection") ' Set objrs = CreateObject("ADODB.Recordset") objConnection.Provider = "Microsoft.Jet.OLEDB.4.0" strSQL = "SELECT label1, date1 " strSQL = strSQL & "FROM values " objConnection.Open objrs.Open strSQL, objConnection x = 1 Do While Not objrs.EOF Range("A" & x) = objrs("label1") Range("B" & x) = objrs("date1") x = x + 1 Loop End Sub Mike Tomasura "Ron" wrote in message ... I have an Access database c:\test\val.mdb In this database I have a table "values". Values has the fields "label1" and "date1". date1 is unique. Now from Excel VBA I want to read label1 from this table with date1 as key. Can someone tell me how this is done? Please be specific (an example would be best) - also indicate which references I need to include in VBA to use Access functionality Ron . |
read Access table from VBA
This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries. http://www.bygsoftware.com/examples/sql.html Or you can get there from the "Excel with Access Databases" section on page: http://www.bygsoftware.com/examples/examples.htm It demonstrates how to use SQL in Excel's VBA to: * create a database, * create a table and add data to it, * select data from a table, * delete a table, * delete a database. DAO and ADO files available. You can also download the demonstration file called "excelsql.zip". The code is open and commented. -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Ron" wrote in message ... I have an Access database c:\test\val.mdb In this database I have a table "values". Values has the fields "label1" and "date1". date1 is unique. Now from Excel VBA I want to read label1 from this table with date1 as key. Can someone tell me how this is done? Please be specific (an example would be best) - also indicate which references I need to include in VBA to use Access functionality Ron |
read Access table from VBA
Ron,
use Data Source=c:\test\val.mdb You might have a problem using "values" in the SQL statement. You might want to rename it to tblValues Private Sub CommandButton1_Click() Set objconnection = CreateObject("ADODB.Connection") ' Set objrs = CreateObject("ADODB.Recordset") connectString = _ "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test\val.mdb" strSQL = "select label1, date1 " strSQL = strSQL & "from tblValues" objconnection.Open connectString objrs.Open strSQL, objconnection x = 1 Do While Not objrs.EOF Range("A" & x) = objrs("label1") Range("B" & x) = objrs("date1") x = x + 1 objrs.movenext Loop End Sub Michael Tomasura -- "Ron" wrote in message ... Mike, just tried it.... where do I make the link to my database c:\test\val.mdb ? Now the Objconnection.open fails - probably because I don't tell where to open. Ron -----Original Message----- Ron, You can use ADO Private Sub CommandButton1_Click() Set objConnection = CreateObject("ADODB.Connection") ' Set objrs = CreateObject("ADODB.Recordset") objConnection.Provider = "Microsoft.Jet.OLEDB.4.0" strSQL = "SELECT label1, date1 " strSQL = strSQL & "FROM values " objConnection.Open objrs.Open strSQL, objConnection x = 1 Do While Not objrs.EOF Range("A" & x) = objrs("label1") Range("B" & x) = objrs("date1") x = x + 1 Loop End Sub Mike Tomasura "Ron" wrote in message ... I have an Access database c:\test\val.mdb In this database I have a table "values". Values has the fields "label1" and "date1". date1 is unique. Now from Excel VBA I want to read label1 from this table with date1 as key. Can someone tell me how this is done? Please be specific (an example would be best) - also indicate which references I need to include in VBA to use Access functionality Ron . |
All times are GMT +1. The time now is 06:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com