ADO - Opening a recordset

M

Malcolm Taylor

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
 
B

Brotha Lee

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
 
M

Malcolm Taylor

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
 
B

Brotha Lee

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)
 
M

Malcolm Taylor

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
 
B

Brotha Lee

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!
 
M

Malcolm Taylor

Hi Brotha

Thank you VERY much for your help - it worked perfectly.

For anyone reading this who wants to see my final code, here it is

=============================
Sub test3()
Dim myConn As ADODB.Connection
Dim myRst As ADODB.Recordset
Dim mytext As String
Dim mydocpath As String
mydocpath = "c:\"

Set myConn = New ADODB.Connection
myConn.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\db4.mdb ;User Id=admin;Password=;"
myConn.Open

Set myRst = New ADODB.Recordset

myRst.ActiveConnection = myConn
myRst.Open "Table1"

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
myConn.Close
Set myConn = Nothing
Set myRst = Nothing

End Sub


=============================
 
Top