Inserting value from MS Word into MS Access

[Evan]

If there are any enlightend souls out there that can help me, I would
greatly appreciate it. I would like to use a macro to take a value and
add it to an access database.

I am having a problem with the very first line of code,
Dim myDatabase as Database, the system does not recognize the Database
object.

PLEASE if there is anyone out there that has a piece of code that
sends info from a word file into a access database... send it to me. I
will Love you forever.

[Trevor Best]

Evan wrote:

If there are any enlightend souls out there that can help me, I would
greatly appreciate it. I would like to use a macro to take a value and
add it to an access database.

I am having a problem with the very first line of code,
Dim myDatabase as Database, the system does not recognize the Database
object.

Add a reference to DAO or use an Object variable and late bind it (set
db=CreateObject("DAO.Database"), although the first way will give you
intellisense.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[Evan]

mmm, I am sorry - I am really new at this... I have added the ADO
reference, and I want to execute this SQL - "insert into documents
(title) values ('testTitle')"

How do I write the actual code, I had it before but then I deleted the
wrong file... now I am back at square one

[Trevor Best]

Evan wrote:

mmm, I am sorry - I am really new at this... I have added the ADO
reference, and I want to execute this SQL - "insert into documents
(title) values ('testTitle')"

How do I write the actual code, I had it before but then I deleted the
wrong file... now I am back at square one

I've not done much in thew way of ADO, at least with Access so I don't
know the connection string to use but it's something like:

Dim cn As New ADODB.Connection
cn.open "connection string here"
cn.Execute "insert..."
cn.close
set cn=nothing

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[Darryl Kerkeslager]

"Trevor Best" <nospam@localhost> wrote in message
news:41***********************@auth.uk.news.easyne t.net...

I've not done much in thew way of ADO, at least with Access so I don't
know the connection string to use but it's something like:

Dim cn As New ADODB.Connection
cn.open "connection string here"
cn.Execute "insert..."
cn.close
set cn=nothing

With ADO in A2000+,

Dim cnxn As ADODB.Connection
Set cnxn = CurrentProject.Connection
cnxn.Execute "INSERT ..."

This works in MDB, not just ADP. I have seen no benefit in closing this
connection (quite the contrary) or setting it to nothing. There is a
benefit in closing and setting recordsets to nothing, of course.

Darryl Kerkeslager

[Trevor Best]

Darryl Kerkeslager wrote:

With ADO in A2000+,

Dim cnxn As ADODB.Connection
Set cnxn = CurrentProject.Connection
cnxn.Execute "INSERT ..."

This works in MDB, not just ADP. I have seen no benefit in closing this
connection (quite the contrary) or setting it to nothing. There is a
benefit in closing and setting recordsets to nothing, of course.

The OP wants to do this from MS Word, so CurrentProject.Connection won't
work :-( It would also be an idea to close it afterwards from there.

BTW I would close the connection in Access if it were a local variable,
I know it just points to an already established connection but it's
still a variable and uses resources, which I wouldn't trust Access VBA
alone to clean it up and release. Too often with all the apps I run, the
system's using 256MB RAM after I close all apps, when I boot up it's
using 128MB so something somewhere is using 128MB RAM and not releasing
it, I wouldn't try to compound that by not cleaning up my variables,
it's bad enough in Windows without me being sloppy and adding to the
problem.

In general, like when I use DAO I have a global Database variable that I
set to the currentdb. This remains available for instant use all the
time from anywhere without having to Set/Open, etc before use. Then I
clean it up before exiting the app.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[Darryl Kerkeslager]

Darryl Kerkeslager wrote:

Dim cnxn As ADODB.Connection
Set cnxn = CurrentProject.Connection
cnxn.Execute "INSERT ..."

This works in MDB, not just ADP. I have seen no benefit in closing this
connection (quite the contrary) or setting it to nothing. There is a
benefit in closing and setting recordsets to nothing, of course.

"Trevor Best" <nospam@localhost> wrote:

BTW I would close the connection in Access if it were a local variable,
I know it just points to an already established connection but it's
still a variable and uses resources, which I wouldn't trust Access VBA
alone to clean it up and release.
The reason I say not to close it is perhaps a quirk of Access, but enough of
a headache for me that I said, "never again": setting the connection to
nothing inside a nested call set the outer connection to nothing as well.
Now, perhaps it was my mistake, and I had used a public cnxn variable, as I
do now - I don't remember - but having to worry about whether my function is
being called by anotehr function using CurrentProject.Connection was too
much effort.

Public cnxn As ADODB.Connection

Private Sub Form_Open()
Set cnxn = CurrentProject.Connection
If CalledFunction() then
cnxn.Execute "INSERT ..."
endif
End Sub

Private Function CalledFunction() As Boolean
Set cnxn = CurrentProject.Connection
cnxn.Execute "DELETE * FROM ..."
Set cnxn = Nothing <-- closes open
connection, creating error in Form_Open
End Function

Then I clean it up before exiting the app.

I could do this, I suppose, but my feeling has been that if it is only one
connection object, no big deal. As I said, I do Set all recordsets =
Nothing when I use them.
Darryl Kerkeslager

[Trevor Best]

Darryl Kerkeslager wrote:

Then I clean it up before exiting the app.

I could do this, I suppose, but my feeling has been that if it is only one
connection object, no big deal. As I said, I do Set all recordsets =
Nothing when I use them.

I do sometimes wonder I bother with the closing and setting to nothing
of a global db variable at the end. I have such db variable opened with
OpenDatabase() to a SQL Server, when I close my app, I close this
connection and set it to nothing. Then even when I close the database
(the actual database window so no mdb is open in Access) my connection
is still there.

As a further test, if I first log onto SQL server as plebe, then close
the mdb (closing ans setting to nothing my db variable to the sql
server) and reopen and log on as "sa", I can't do sa type things. This
could cause a severe security breach the other way around, i.e. plebe
could have sa privileges.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[Darryl Kerkeslager]

"Trevor Best" <nospam@localhost> wrote:

As a further test, if I first log onto SQL server as plebe, then close
the mdb (closing ans setting to nothing my db variable to the sql
server) and reopen and log on as "sa", I can't do sa type things. This
could cause a severe security breach the other way around, i.e. plebe
could have sa privileges.

I'm not very familiar with SQL Server, but this sounds kinda bad...