Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Connection of Excel 07 pivot table to Access Query makes DB read o | Excel Discussion (Misc queries) | |||
How do I access the read-only box on my disc? | Excel Discussion (Misc queries) | |||
Query from Access into Excel cause Access to go to read only | Excel Discussion (Misc queries) | |||
Cannot access read-only documents. | Excel Discussion (Misc queries) | |||
Sumproduct in Excel Spreadsheet to read Access db table | Excel Worksheet Functions |