Jump to content


Photo

[SOLVED]Need Help writing a Microsoft Word Macro


  • Please log in to reply
1 reply to this topic

#1 Tirus

Tirus

    P2L Jedi

  • Members
  • PipPipPip
  • 764 posts
  • Gender:Male
  • Location:Montreal, Canada
  • Interests:Web Design, Programming, Music, Martial Arts

Posted 04 June 2008 - 02:58 PM

I am trying to create a Word macro that will connect to an Access database (stored on my computer), execute a number of SELECT queries (each with different conditions) and then display the results throughout my word file.

I have spent the last two days trying to figure this out...I have no previous knowledge of Visual Basic 6 (I am using what came with Office, which is Visual Basic 6.3).

I am using Word 2003 and Access 2000 (I also have Access 97 if need be).

Now I have read about ADO, DAO, ODBC and ADODB, DSN and DSN-less connections or SOMETHING along those lines and to be honest I am a little lost.

I have tried many different simple examples of code, but none seem to work. I usually end up with an error message saying that the Object was not defined, for which case I read that the solution is to check the References library and make sure the appropriate Reference is checked, which I did.

Here is an example of code...is there anything wrong with it:
Sub DBConnect()
	Dim Cmd As New ADODB.Command
	Dim sqlString As String
	Dim RS As ADODB.Recordset
	Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Template\evaluations.mdb"
	Conn.Open
	sqlString = "SELECT * FROM data2"
	Cmd.CommandText = sqlString
	Cmd.CommandType = adCmdText
	Set RS = Cmd.Execute
End Sub
The error message I get for that is Run-time error '424': Object required.

I've tried commenting out some of the code, leaving only the first 3 lines, and I do not get any errors...but as soon as I uncomment the 4th line, I get that error.

Does anyone know how to make a simple connection from Word to Access and pull out certain results? Where have I gone wrong, what am I missing? Again, I have no previous experience with VB6 and would appreciate any and all help.

Thanks,

Tirus

Edited by Tirus, 09 June 2008 - 09:06 PM.


#2 Tirus

Tirus

    P2L Jedi

  • Members
  • PipPipPip
  • 764 posts
  • Gender:Male
  • Location:Montreal, Canada
  • Interests:Web Design, Programming, Music, Martial Arts

Posted 07 June 2008 - 01:16 PM

ok, so no help so far...it is understandable though since I know most people on P2L are geared more towards html, php, css, photoshop, flash etc..

so just an update, maybe this will help a bit:

i've spent the last 3 days delving into the deep dark places of Google, finding examples of code for Visual Basic 6 which connect to an Access database. I did find one that was promising, and after some work, I think i am SO CLOSE to having i work correctly, but I am getting an error. The code was written by someone else and posted in a thread on some forum..I would contact them but the thread is about 4 years old so the chances of that are well.....slim. Anyways, heres the code:
'Connection and recordset objects
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
'A string to store the SQL statement
Dim strSQL As String

Sub Form_Load()
'Instantiate recordset and connection
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

'You haven't told me the structure of your database so you will have to change the following SQL statement
'to reflect your db structure
'For this example the DB contains a table called Person, which has two fields
'Name and address
Dim strSearchCriteria As String
strSearchCriteria = "Adam G"
strSQL = "SELECT Name FROM data2 WHERE Name LIKE '" & strSearchCriteria & "%'"

'You will need to construct the connection string yourself
With con
		.Provider = "MSDASQL.1"
		.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Template\evaluations.mdb;Uid=Admin;Pwd=;"
		.Open
End With

'Create a recordset and fill it with the records returned by the SQL statement
rs.Open strSQL, con

'You can now move through the records and display them on the screen
'In this example I have two labels on the form: lblName and lblAddress

'Check the recordset is not empty
If ((rs.EOF <> True) And (rs.BOF <> True)) Then

'Display the Record
'I am deliberately using two different methods of referring to the fields

'Either by name, using a bang (!)
lblName.Caption = rs!Name

'Or relatively, using a subscript - note that this starts from 0
lbl1a.Caption = rs.Fields(1)
End If

'Close the connection and recordset
Set rs = Nothing
rs.Close
cn.Close
End Sub

So, I get an error on the line
lblName.Caption = rs!Name
and the error says:
Run-time error '424': Object required. After doing some reading, I've come to the conclusion that I've neglected to define some object, perhaps lblName. So I went to Word, created a label and called it lblName, but i still doesn't work. My question is, what is the .Caption part of the code for, what does it do? Do I need to create something else in Word?

I'm sure I am missing something really stupid and quite simple but again, with no previous experience with this, it's somewhat challenging.

Again, any and all help, tips or even a little insight would be greatly appreciated.

Many thanks,

Tirus

EDIT:Solution: http://word.mvps.org...tDataFromDB.htm

Edited by Tirus, 09 June 2008 - 09:05 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users