Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to query an access database from within word.

    I want to compare a documents issue number (placed on the document), with what a database says it is.
    eg
    on the word forum

    Issue 1
    KL-001.F1

    These are set up as bookmarks on the document, then read it variable by the word document on startup.

    Variable MyIssue = '1'
    Variuable MyDoc = 'KL-001.F1'

    This bit works ok.

    The next bit needs to query the access database, lookup the name of the form, and what version the database things it is.

    This is the code i have so far:-

    (I have added Microsoft ActiveX Data Objects version 2.8 into the words references set)

    Dim dbConnectStr As String
    Dim conn As ADODB.Connection
    Dim dbPath As String
    Dim strSQL As String
    Dim rs As ADODB.Recordset

    dbPath = "S:\My Path\MyDatabase_BE.mdb"
    dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"

    strSQL = "Select [Issue Number] from Documents where [Reference Number] = " & MyDoc

    Set conn = New ADODB.Connection

    conn.ConnectionString = dbConnectStr
    conn.Open

    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn
    MsgBox rs.strSQL


    End Sub


    [Issue Number] is the field that i want from the table [Documents].
    [Reference Number] is the name of the document in the access table, and MyDoc is the variable in word.

    Currently, its coming up with an error 'Compile Error, method or data member not found'

    I need to place the number taken from the access database into a variable, so i can compare it with MyIssue (the number on the actual word form)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    On which line do you get the compile error?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    MsgBox rs.strSQL

    almost the final line, it highlights, the .strSQL bit.

    I did try to change the SQL to something more basic like :-

    strSQL = "Select * from Documents"

    but it still does it.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The ADO Recordset object does not have a member called strSQL, hence the error.
    What exactly do you want to show in the messagebox?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='pieterse' post='773905' date='06-May-2009 13:22']The ADO Recordset object does not have a member called strSQL, hence the error.
    What exactly do you want to show in the messagebox?[/quote]

    I don't actually want a messagebox, i need to place, in a variable, the Issue Number, from the access database.

    The field i want is called [Issue Number], (a number) from a table called Documents.

    Where, [Reference Number] (another string field in the access table) is equal to MyDoc (a string in the word doc)

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    First you have to test whether there is any record in the recordset, one way:

    If Not rs.EOF Then
    rs.MoveFirst
    End If

    Then you can get a field value:

    If Not rs.EOF Then
    rs.MoveFirst
    MsgBox rs.Fields("Issue Number").Value
    End If
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is where i am so far: I appear to be able to connect from word, to an access database using this code:

    Now, i need a way to find a record and an associated value. (I did try the code suggested above, but it did not work)

    Dim dbConnectStr As String
    Dim conn As ADODB.Connection
    Dim dbPath As String
    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Set conn = New ADODB.Connection

    dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Password=mypass; User ID=myUID;Data Source=S:\Document Library\Mydatabase.mdb;Jet OLEDB:System database=S:\MyPath\SYSTEM.mdw;Jet OLEDB:System Database=S:\Document Library\SYSTEM.mdw;"

    conn.ConnectionString = dbConnectStr
    conn.Open
    Set rs = New ADODB.Records

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, this works on a database I have:
    Code:
    Sub Test()
    	Dim dbConnectStr As String
    	Dim conn As ADODB.Connection
    	Dim dbPath As String
    	Dim strSQL As String
    	Dim rs As ADODB.Recordset
    
    	dbPath = "S:\My Path\MyDatabase_BE.mdb"
    	dbPath = "c:\data\jkpads.mdb"
    	dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
    
    	'strSQL = "Select [Issue Number] from Documents where [Reference Number] = " & MyDoc
    	strSQL = "Select * FROM Comments where commentdate=#19-4-2009 17:03:28#"
    
    	Set conn = New ADODB.Connection
    
    	conn.ConnectionString = dbConnectStr
    	conn.Open
    
    	Set rs = New ADODB.Recordset
    	rs.CursorLocation = adUseClient
    	rs.Open strSQL, conn
    	If rs.RecordCount > 0 Then
    		MsgBox rs.Fields("comment").Value
    	End If
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Will try that again, thanks pieterse

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •