Inserting Database data into User Form on word using vba

[keith.hyland]

Hi,

I am trying to create a userForm on a word doc that gets populated with
data from an Odbc data source.

I have recoreded a VBA macro where I retrieve the data from the
datasource, and insert it onto the page (using the
ActiveDocument.Range.InsertDatabase construct). So I have the code for
making DB connection, and the SQl code ready to go.

The problem is that I want to have this data appear in a ListBox (or
some other element which allows the user to select a single row) on a
UserForm not on the page itself.

Looking at the documentation the InsertDatabase function needs to be
called on a Range object.. I am trying to access a Range object on the
UserForm but can't seem to find one. Is there a way I can access a
Range object on a UserForm?

Or is there some other way I can call the InsertDatabase function to
insert the data onto a UserForm.

Or is there another way instead of the InsertDatabase function to
access the data in the database?

Cheers,
Keith

 

[Doug Robbins]

Private Sub UserForm_Initialize()

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim NoOfRecords As Long

' Open the database

Set db = OpenDatabase("D:\Access\ResidencesXP.mdb")

' Retrieve the recordset

Set rs = db.OpenRecordset("SELECT * FROM Owners")

' Determine the number of retrieved records

With rs

.MoveLast

NoOfRecords = .RecordCount

.MoveFirst

End With

' Set the number of Columns = number of Fields in recordset

ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records

ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup

rs.Close

db.Close

Set rs = Nothing

Set db = Nothing

End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[khylo]

Thanks for that,

However I want to connect to a ODBC source.. Not a Access DB. Is there
a way to call the openDatabase method and specify a ODBC DSN.

Thanks,
Keith

 

[khylo]

Ok, I've been poking about and discovered the following..

Stil having problems though, but maybe this information will spark off
an answer..

I have found the ADO object and am trying to use that...

This results in a Error 80004005 "Data Source Name Not Found"... But
the data source is there because my previous call to
ActiveDocument.Range.InsertDatabase worked... Any ideas?

Dim db As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim connString As String
conString = "DSN=vba;DESC=Vba
Test;Database=vba;OPTION=0;PASSWORD=password;PORT=0;SERVER=127.0.0.1;UID=vba"
db.Open connString, "vba", "password"
Rs.ActiveConnection = db
Rs.Source = "SELECT test_0.date, test_0.data FROM vba.test test_0
WHERE (test_0.date>={ts '1971-01-01 00:00:00'}) ORDER BY test_0.date"
Rs.Open
MsgBox Rs(0)
Rs.Close

 

[khylo]

I have solved this (using ADO)..
http://khylo.blogspot.com/2005/10/db-access-in-word-vba.html