Word database connection

[seany]

Hi,

I am trying to connect to access database. It keeps on saying that it is a unrecognized format of databae. Code is below

Thanks

Dim SQL As String
Dim Db As DAO.Database
Dim rs As DAO.Recordset

SQL = "SELECT Tbl_Stage2.Step_ID, * FROM Tbl_Stage2_Step LEFT JOIN Tbl_Stage2 ON Tbl_Stage2_Step.Stage2_Step_ID = Tbl_Stage2.Step_ID WHERE (((Tbl_Stage2_Step.Delete) = 0) And ((Tbl_Stage2.Step_ID) Is Null)) ORDER BY Tbl_Stage2_Step.Sort;"
Set Db = OpenDatabase("C:\Documents and Settings\sean.clemie\Desktop\Road Adoptions.mdb")
Set rs = Db.OpenRecordset("SQL")
If Not rs.BOF Then
rs.MoveFirst
Do Until rs.EOF
MsgBox rs("Stage2_Step_ID")
rs.MoveNext
Loop
End If
rs.Close

 

[reclusivemonkey]

You need to set up ODBC to connect to another database.

 

[seany]

Only no how to do DAO connections. I am trying to connect to access from word?

Thanks

 

[nateobot]

Hi,

I am trying to connect to access database. It keeps on saying that it is a unrecognized format of databae. Code is below

Thanks

Dim SQL As String
Dim Db As DAO.Database
Dim rs As DAO.Recordset

SQL = "SELECT Tbl_Stage2.Step_ID, * FROM Tbl_Stage2_Step LEFT JOIN Tbl_Stage2 ON Tbl_Stage2_Step.Stage2_Step_ID = Tbl_Stage2.Step_ID WHERE (((Tbl_Stage2_Step.Delete) = 0) And ((Tbl_Stage2.Step_ID) Is Null)) ORDER BY Tbl_Stage2_Step.Sort;"
Set Db = OpenDatabase("C:\Documents and Settings\sean.clemie\Desktop\Road Adoptions.mdb")
Set rs = Db.OpenRecordset("SQL")
If Not rs.BOF Then
rs.MoveFirst
Do Until rs.EOF
MsgBox rs("Stage2_Step_ID")
rs.MoveNext
Loop
End If
rs.Close

The SQL in the bolded line about should not be in quotes.

 

[seany]

Change this and still getting the same problem

Thanks

Sean

 

[Bat17]

I just tried a cut down version of your code and it worked OK.
Are you sure the DB is valid?
Are you getting the error on the "Set Db =" line?

Peter

 

[seany]

Getting a error on Set Db = line.

Works ok from access to access so path must be valid. What reference did you turn on in word? 3.51 or 3.60? Can you post or email me a copy of your example please?

Thanks

Sean

 

[Bat17]

Here is the code I used Windows XP home, Word2K, Access2K, reference in word set to DAO 3.6. using the Northwind sample database that comes with Access.

Sub test()
Dim SQL As String
Dim Db As DAO.Database
Dim rs As DAO.Recordset
SQL = "SELECT * FROM Customers WHERE  Customers.CustomerID='BOLID';"
Set Db = OpenDatabase("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")
Set rs = Db.OpenRecordset(SQL)
If Not rs.BOF Then
    rs.MoveFirst
    Do Until rs.EOF
        MsgBox rs("CompanyName")
        rs.MoveNext
    Loop
End If
rs.Close
End Sub

I will email the Word doc to you as well. I would recomend that you then edit out your email address above before the spam bots find it

Peter

 

[seany]

Thanks work when I changed to DAO 3.60 reference. Thanks. Do you know how to input text in to work document? Do you have to put some sort of text box in?

Thanks alot

Sean

 

[Bat17]

Check out Helen Feddema's website for some code samples of Working Access to Word

http://www.helenfeddema.com/CodeSamples.htm

Peter