accessing/updating Access table with Word VBA 
Author Message
 accessing/updating Access table with Word VBA

Help!

We're using a Word document as requisition.  We need to
generate a unique requisitoin number.  We can write the
number to a text document and increment it using VBA.  
This doesn't guarantee that we'll get a unique number
though.  So I set up an access database with a simple
table containing a autonumber field and a date field.  
Referencing the DAO 3.6 Object Library, I am able to open
a recordset, but am unable to figure out how to add a new
record to the table (increment the recordset).

These are just the options I came up with, I'm sure there
is an easier way to do this...  Any ideas or suggestions
would be greatly appreciated!

Kevin



Mon, 05 Sep 2005 03:39:59 GMT  
 accessing/updating Access table with Word VBA
Hi Kevin,

Regular contributor Perry from the Netherlands posted this here a while
back:

Quote

Following example uses:
MS Access table (tblDoc) consisting of:
Fld ID - FieldName - Type
1 - Number - Long Int (AutoIncrement)
2 - DocDate - Date/Time
3 - DocName - String

ADO to connect to yr Access database
Inserts a new record (note: ActiveDocument.FullName)
and picks up the new number as created by AutoIncrement functionality.

Krgrds,
Perry

'== begin code

    Const dbProv = "Microsoft.Jet.OLEDB.4.0"
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim ds As String, sSQL As String
    Dim MyNumber

    'location/name of database
    ds = "D:\MyDocs\Access\db6.mdb"
    'insert a new record
    sSQL = "INSERT INTO tblDoc ( [Date], Name ) " & _
            "SELECT Now() AS DocDate, '" & _
            ActiveDocument.FullName & "' AS DocName;"
    'setting up connection
    Set cn = New ADODB.Connection
    With cn
        .Provider = dbProv
        .ConnectionString = "Data Source=" & ds
        .Open
        'insert new record
        .Execute sSQL
    End With
    'setup recordset object
    Set rs = New ADODB.Recordset
    'retrieve new number
    rs.Open "SELECT Number FROM tblDoc", cn, adOpenKeyset
    rs.MoveLast

    MyNumber = rs(0)    '<< pick it up
    'close ADO object vars
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing

    'display new number
    MsgBox MyNumber

'== end code

Unquote

Please respond to the newsgroups for the benefit of others who may be
interested.

Hope this helps
Doug Robbins - Word MVP

Quote:
> Help!

> We're using a Word document as requisition.  We need to
> generate a unique requisitoin number.  We can write the
> number to a text document and increment it using VBA.
> This doesn't guarantee that we'll get a unique number
> though.  So I set up an access database with a simple
> table containing a autonumber field and a date field.
> Referencing the DAO 3.6 Object Library, I am able to open
> a recordset, but am unable to figure out how to add a new
> record to the table (increment the recordset).

> These are just the options I came up with, I'm sure there
> is an easier way to do this...  Any ideas or suggestions
> would be greatly appreciated!

> Kevin



Mon, 05 Sep 2005 05:33:42 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Copying an OLE object from an Access table to Word via VBA

2. accessing FoxPro tables in Word Combo Box with VBA

3. word VBA won't let me add a new record to an access table

4. VBA - Access 2000 Create a table and access the fields

5. Word accessing unlicensed Access tables

6. Exec. Word VBA Function From Access using VBA

7. Access 97 VBA v Access 2K VBA - different ?

8. Access 2000 VBA Handbook vis a vis Access 97 VBA Handbook

9. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

10. move between Access and Word, back to Access, Close word

11. open Access table = locked Access table??

12. open Access table = locked Access table??

 

 
Powered by phpBB® Forum Software