ADO - Opening a recordset

G

Guest

Hi

I have a word macro as follows....

================
Sub test()
Dim myDbase As Database
Dim myrst As DAO.Recordset
Dim myDbasePath, myDbaseTable, myDocPAth, myText As String
Dim temp

myDbasePath = "c:\db4.mdb"
myDbaseTable = "Table1"
myDocPAth = "c:\"

Set myDbase = OpenDatabase(myDbasePath)
Set myrst = myDbase.OpenRecordset(myDbaseTable)

myrst.MoveFirst

While myrst.EOF = False
myText = myrst.Fields(0) & " - " & myrst.Fields(1) & " " & myrst.Fields(2) &
" " & myrst.Fields(3)
Set temp = New Document
With temp
.PageSetup.DifferentFirstPageHeaderFooter = True
.Sections(1).Headers.Item(wdHeaderFooterFirstPage).Range.InsertAfter
(myText)
.SaveAs (myDocPAth & myrst.Fields(0).Value & ".doc")
.Close
End With
myrst.MoveNext
Wend
myrst.Close
End Sub
===============
It uses DAO. What I would like to be able to do is to get the macro to do
the same thing using ADO commands.

I have tried to find out the syntax of the Recordset.open command but, as
yet, have not come up with a solution.

Any help would be gratefully appreciated.

Many thanks

Malcolm
 
G

Guest

Malcolm,

The following code should work. I assumed that you are running the code
directly from a Acces database.

Dim myConn As New ADODB.Connection
Dim myRst As New ADODB.Recordset

Set myConn = CurrentProject.Connection
Set myRst = New ADODB.Recordset

With myRst
..ActiveConnection = myConn
..Open (YourSQL)
End With

myRst.Close
myConn.Close
Set myConn = Nothing
 
G

Guest

Hi brotha and thanks for the reply.

However, I am running the code from a Word macro, so I need to be able to
use the ADO to pick up the path and filename of the Access database and the
name of the table, if that is possible. Thanks Malcolm
 
G

Guest

Malcolm,

Add the following piece of code to the previous code
dim myConnString as string
set myConnstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
myDbasePath &";User Id=admin;Password=; "

and replace Set myConn = CurrentProject.Connection line with the following
piece of code

with myConn
..connectionstring = myConnstring
..open
end with

That should work.

(BTW the connectionstring used is that for a database which is not password
protected. For more connectionstring see www.connectionstrings.com)
 
G

Guest

Hi Brotha and thanks for the reply.

One final question, I think!!

How do I specify the name of the table?

In the line .open, do I just write .open nameofmytable

Many thanks for all your help.

Malcolm
 
G

Guest

Hi Malcom,

Yes in the .open command (of the recordset) you enter the name of your table
(or a SQL string if you do not want the entire table to be selected)

Hope it works!
 
Top