Writing Data from word to an Access table 1

[ceyhorn]

How would I go about opening an access table from word and then inputing data into that table with data stored in a variable?

Thanks in advance,

Chris

 

[TonyJollans]

Hi ceyhorn,

Something like this should get you going ..

[blue]Dim appAccess As New Access.Application
Dim objRecordSet As DAO.Recordset

appAccess.OpenCurrentDatabase ("[i]YourPath\YourDb.mdb[/i]")
Set objRecordSet = appAccess.DBEngine(0)(0).OpenRecordset("[i]YourTable[/i]")
With objRecordSet
    .AddNew
    ![i]YourField[/i] = "[i]Alpha Value[/i]" [green]' or variable name[/green]
    ![i]YourNumField[/i] = [i]12345[/i] [green]' or variable name[/green]
    .Update
    .Close
End With
Set objRecordSet = Nothing
appAccess.Quit
Set appAccess = Nothing[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[ceyhorn]

Is !YourField, the name of the columns in my table?

Thanks again,
chris

 

[TonyJollans]

Hi Chris,

Absolutely! All the text in Italics needs replacing with real names - database, table, columns, values.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[ceyhorn]

When I add in the names of my fields it gives me a compile error. Also, what reference libraries do i need to check besides word and access?

.AddNew
!Application Number = appnum ' or variable name
!Organization Name = targetgroup ' or variable name
.Update
.Close

thanks a million,
Chris

 

[TonyJollans]

Hi Chris,

I think you'll need references to both ..

Microsoft Access x.y Object Library, and
Microsoft DAO x.y Object Library



If your column names have spaces in them use underscores instead of the spaces or put the names in brackets. For your Column named Application Number, use either ..

[blue][tt]!Application[red]_[/red]Number[/tt][/blue]

.. or ..

[blue][tt]![red][[/red]Application Number[red]][/red][/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[ceyhorn]

Ok, one more question. When i run the app on some of the word files that have already been run, i get a error message 3322 saying that the .update cannot run because of a duplicate, how can i put some error checking in to bypass this?

chris

 

[TonyJollans]

Hi Chris,

You need to have error trapping just the same as for any other error you might get. I don't know what you want to do, but here's one option ..

[blue]:
:
.Addnew
:
[green]' fill your fields[/green]
:
[red]On Error Resume Next[/red]
.Update
[red]If Err.Number = 3022 Then
    [green]' Duplicate key - ignore if that's what you want[/green]
Else
    [green]' Some other error - better take some action[/green]
End If[/red]
.Close[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[ceyhorn]

Tony,

Thanks for all of the help, it has been very valuable.

Chris