Establish connection with and transferring data to Access

[Greg Maxey]

I found some code posted by Doug Robbins for transferring data from a
Word formfield to an Access data base. I incorporated that code into a
macro for collecting the results for Word Forms. I have a few
questions:

When if first tried to run Doug's code I got an error on the line:

Dim vConnection As New ADODB.Connection

From past experience I figured that this was due to a missing

reference. From there it was hunt and peck. I had to just load all
of them that I thought were related to Access or Objects to get the
code to run and then remove some a block at a time until I narrowed it
down to the one I needed.

How do you know or how can you determine what references are required
to run your code?

Doug's code opens and writes to an existing database. I would prefer
creating a new database and defining the fields in my macro. I think
(I don't know) that if I can figure out how to "create a new" database
vice "open" and existing database that I might be able to define the
structure.

I realize this question might be better suited for an Access group, but
does anyone reading know how to create a new database and table from
VBA in Word.

Doug's code uses "Provider=Microsoft.Jet.OLEDB.4.0;" Doug, and rocket
scientist excluded, how would anyone attempting to write code with
formal training know to use something like that? Is that the way or
one of many ways? If there are others perhaps more straigtforward I
would appreciate seeing a few examples.

Regulars here know that I have a website and I think that showing a
method for collecting data from Word formfields into an Access database
would be helpful to other users. If it exists out there as a FAQ or
website I dont' know about it and spent the better part of a day
scatching together what I have. I certainly would like hear your
comments and suggestions for improvement.


Sub TallyData4()
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim myDoc As Word.Document

oPath = GetPathToUse
If oPath = "" Then
MsgBox "A folder was not selected"
Exit Sub
End If
'Identify files names
oFileName = Dir$(oPath & "*.doc")
ReDim FileArray(1 To 1000) 'A number larger the expected number of
replies
'Add file name to the array
Do While oFileName <> ""
i = i + 1
FileArray(i) = oFileName
'Get the next file name
oFileName = Dir$
Loop
'Resize and preserve the array
ReDim Preserve FileArray(1 To i)
Application.ScreenUpdating = False
'Provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=C:\TestDataBase.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)
vRecordSet.AddNew
With myDoc
If .FormFields("Text1").Result <> "" Then _
vRecordSet!Name = .FormFields("Text1").Result
If .FormFields("Text2").Result <> "" Then _
vRecordSet("Favorite Food") = .FormFields("Text2").Result
If .FormFields("Text3").Result <> "" Then _
vRecordSet("Favorite Color") = .FormFields("Text3").Result
.Close
End With
Next i
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
Application.ScreenUpdating = True
End Sub

 

[Jezebel]

How do you know or how can you determine what references are required
to run your code?

Experiment with the object model or check the documentation for the library
you want to use.

Doug's code opens and writes to an existing database. I would prefer
creating a new database and defining the fields in my macro. I think
(I don't know) that if I can figure out how to "create a new" database
vice "open" and existing database that I might be able to define the
structure.

I realize this question might be better suited for an Access group, but
does anyone reading know how to create a new database and table from
VBA in Word.

An Access group might help, but what you're working with here is the JET
database engine. Access uses it, but so do other databases. Download the ADO
helpfile from the Microsoft site and do some homework Also look at
http://www.freevbcode.com/ShowCode.Asp?ID=75 for a sample VB app that does
most of the things you'll need.

Creating a new database in code is simple enough in principle, but it needs
a sh*t-load of code to do it. You have to work your way through defining a)
the tables, b) the fields in each table, c) the indexes for each table, and
d) the relationships. Unless you have seriously pressing reasons for
creating the database on the fly, it is *much* easier to create your
database using Access or MySQL, then just use it in your code.

Doug's code uses "Provider=Microsoft.Jet.OLEDB.4.0;" Doug, and rocket
scientist excluded, how would anyone attempting to write code with
formal training know to use something like that? Is that the way or
one of many ways? If there are others perhaps more straigtforward I
would appreciate seeing a few examples.

It's in the documentation.


In respect of your code, consider whether you should do ALL your data work
entirely with SQL statements, eg instead of using "vRecordSet.AddNew
.....Fields(x) = ... Update" you use something

Dim pSQL as string
pSQL = "INSERT INTO MyTable (Text1, Text2, Text2) VALUES (" &
MyDoc.FormFields("Text1").Result .... & ");"
vConnection.Execute pSQL

It's part of the standard

Some advantages of this approach ---

- It works with any SQL-compliant database. (which is pretty well all of
them)
- Within your application you can separate the database functions from your
main code. Put all the database work (finding, opening, closing, etc) into a
separate class; your main code simply passes the SQL string to the class.
That way your main code doesn't care what sort of database connection you
have, and all your DB errors (there tend to be a lot when you're just
starting out) are all in one place. You'll also need to add some functions
for cleaning up strings, preparing dates, bracketing table and field names
if necessary, etc.
- It's more flexible than hard-coding field names and types.


If you're writing user-entered form fields directly into a database, you
should at least be aware of the risks of SQL-injection.




And a separate point: to check if a string is empty, it's much more
efficient to check if the length is zero [ len(MyString) = 0 ] than to look
at the string content [ MyString = "" ] In VBA, strings are stored as a
header comprising the address and length, and the body. Checking if the
string = "" means retrieving the adderess and length, using them to retrieve
the string itself, then doing a character comparison. Checking the length
means retrieving the header only.

 

[Greg Maxey]

Jezebel,

Thanks for the condensed introduction to graduate level work. I humbly
submit that I have a lot to learn and if I can find the motivation I will be
busy for a while.

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

How do you know or how can you determine what references are required
to run your code?

Experiment with the object model or check the documentation for the
library you want to use.

Doug's code opens and writes to an existing database. I would prefer
creating a new database and defining the fields in my macro. I think
(I don't know) that if I can figure out how to "create a new"
database vice "open" and existing database that I might be able to
define the structure.

I realize this question might be better suited for an Access group,
but does anyone reading know how to create a new database and table
from VBA in Word.

An Access group might help, but what you're working with here is the
JET database engine. Access uses it, but so do other databases.
Download the ADO helpfile from the Microsoft site and do some
homework Also look at http://www.freevbcode.com/ShowCode.Asp?ID=75
for a sample VB app that does most of the things you'll need.

Creating a new database in code is simple enough in principle, but it
needs a sh*t-load of code to do it. You have to work your way through
defining a) the tables, b) the fields in each table, c) the indexes
for each table, and d) the relationships. Unless you have seriously
pressing reasons for creating the database on the fly, it is *much*
easier to create your database using Access or MySQL, then just use
it in your code.

Doug's code uses "Provider=Microsoft.Jet.OLEDB.4.0;" Doug, and
rocket scientist excluded, how would anyone attempting to write code
with formal training know to use something like that? Is that the
way or one of many ways? If there are others perhaps more
straigtforward I would appreciate seeing a few examples.

It's in the documentation.


In respect of your code, consider whether you should do ALL your data
work entirely with SQL statements, eg instead of using
"vRecordSet.AddNew ....Fields(x) = ... Update" you use something

Dim pSQL as string
pSQL = "INSERT INTO MyTable (Text1, Text2, Text2) VALUES (" &
MyDoc.FormFields("Text1").Result .... & ");"
vConnection.Execute pSQL

It's part of the standard

Some advantages of this approach ---

- It works with any SQL-compliant database. (which is pretty well all
of them)
- Within your application you can separate the database functions
from your main code. Put all the database work (finding, opening,
closing, etc) into a separate class; your main code simply passes the
SQL string to the class. That way your main code doesn't care what
sort of database connection you have, and all your DB errors (there
tend to be a lot when you're just starting out) are all in one place.
You'll also need to add some functions for cleaning up strings,
preparing dates, bracketing table and field names if necessary, etc.
- It's more flexible than hard-coding field names and types.


If you're writing user-entered form fields directly into a database,
you should at least be aware of the risks of SQL-injection.




And a separate point: to check if a string is empty, it's much more
efficient to check if the length is zero [ len(MyString) = 0 ] than
to look at the string content [ MyString = "" ] In VBA, strings are
stored as a header comprising the address and length, and the body.
Checking if the string = "" means retrieving the adderess and length,
using them to retrieve the string itself, then doing a character
comparison. Checking the length means retrieving the header only.

Regulars here know that I have a website and I think that showing a
method for collecting data from Word formfields into an Access
database would be helpful to other users. If it exists out there as
a FAQ or website I dont' know about it and spent the better part of
a day scatching together what I have. I certainly would like hear
your comments and suggestions for improvement.


Sub TallyData4()
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim myDoc As Word.Document

oPath = GetPathToUse
If oPath = "" Then
MsgBox "A folder was not selected"
Exit Sub
End If
'Identify files names
oFileName = Dir$(oPath & "*.doc")
ReDim FileArray(1 To 1000) 'A number larger the expected number of
replies
'Add file name to the array
Do While oFileName <> ""
i = i + 1
FileArray(i) = oFileName
'Get the next file name
oFileName = Dir$
Loop
'Resize and preserve the array
ReDim Preserve FileArray(1 To i)
Application.ScreenUpdating = False
'Provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=C:\TestDataBase.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset,
adLockOptimistic 'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)
vRecordSet.AddNew
With myDoc
If .FormFields("Text1").Result <> "" Then _
vRecordSet!Name = .FormFields("Text1").Result
If .FormFields("Text2").Result <> "" Then _
vRecordSet("Favorite Food") = .FormFields("Text2").Result
If .FormFields("Text3").Result <> "" Then _
vRecordSet("Favorite Color") = .FormFields("Text3").Result
.Close
End With
Next i
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
Application.ScreenUpdating = True
End Sub

 

[Greg Maxey]

Jezebel,

I couldn't locoate a ADO help file at microsoft.support

Do you know where the download can is located or the specific name?

I also cannot get this SQL statement to work. I tied to peel away
everything but just the basics to see if I could get it to work and then
build on it, but I get an error on the .Execute stating "No value given for
one or more required parameters." Can you tell me what "parameter" I am
missing?

Thanks

Sub Testing()
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim pSQL As String
vConnection.ConnectionString = "data source=E:\My
Documents\Batch\TestDataBase2.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
vConnection.Execute "DELETE * FROM MyTable"
pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES(TestText1,
TestText2, TestText3)"
vConnection.Execute pSQL
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
End Sub




--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

How do you know or how can you determine what references are required
to run your code?

Experiment with the object model or check the documentation for the
library you want to use.

Doug's code opens and writes to an existing database. I would prefer
creating a new database and defining the fields in my macro. I think
(I don't know) that if I can figure out how to "create a new"
database vice "open" and existing database that I might be able to
define the structure.

I realize this question might be better suited for an Access group,
but does anyone reading know how to create a new database and table
from VBA in Word.

An Access group might help, but what you're working with here is the
JET database engine. Access uses it, but so do other databases.
Download the ADO helpfile from the Microsoft site and do some
homework Also look at http://www.freevbcode.com/ShowCode.Asp?ID=75
for a sample VB app that does most of the things you'll need.

Creating a new database in code is simple enough in principle, but it
needs a sh*t-load of code to do it. You have to work your way through
defining a) the tables, b) the fields in each table, c) the indexes
for each table, and d) the relationships. Unless you have seriously
pressing reasons for creating the database on the fly, it is *much*
easier to create your database using Access or MySQL, then just use
it in your code.

Doug's code uses "Provider=Microsoft.Jet.OLEDB.4.0;" Doug, and
rocket scientist excluded, how would anyone attempting to write code
with formal training know to use something like that? Is that the
way or one of many ways? If there are others perhaps more
straigtforward I would appreciate seeing a few examples.

It's in the documentation.


In respect of your code, consider whether you should do ALL your data
work entirely with SQL statements, eg instead of using
"vRecordSet.AddNew ....Fields(x) = ... Update" you use something

Dim pSQL as string
pSQL = "INSERT INTO MyTable (Text1, Text2, Text2) VALUES (" &
MyDoc.FormFields("Text1").Result .... & ");"
vConnection.Execute pSQL

It's part of the standard

Some advantages of this approach ---

- It works with any SQL-compliant database. (which is pretty well all
of them)
- Within your application you can separate the database functions
from your main code. Put all the database work (finding, opening,
closing, etc) into a separate class; your main code simply passes the
SQL string to the class. That way your main code doesn't care what
sort of database connection you have, and all your DB errors (there
tend to be a lot when you're just starting out) are all in one place.
You'll also need to add some functions for cleaning up strings,
preparing dates, bracketing table and field names if necessary, etc.
- It's more flexible than hard-coding field names and types.


If you're writing user-entered form fields directly into a database,
you should at least be aware of the risks of SQL-injection.




And a separate point: to check if a string is empty, it's much more
efficient to check if the length is zero [ len(MyString) = 0 ] than
to look at the string content [ MyString = "" ] In VBA, strings are
stored as a header comprising the address and length, and the body.
Checking if the string = "" means retrieving the adderess and length,
using them to retrieve the string itself, then doing a character
comparison. Checking the length means retrieving the header only.

Regulars here know that I have a website and I think that showing a
method for collecting data from Word formfields into an Access
database would be helpful to other users. If it exists out there as
a FAQ or website I dont' know about it and spent the better part of
a day scatching together what I have. I certainly would like hear
your comments and suggestions for improvement.


Sub TallyData4()
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim myDoc As Word.Document

oPath = GetPathToUse
If oPath = "" Then
MsgBox "A folder was not selected"
Exit Sub
End If
'Identify files names
oFileName = Dir$(oPath & "*.doc")
ReDim FileArray(1 To 1000) 'A number larger the expected number of
replies
'Add file name to the array
Do While oFileName <> ""
i = i + 1
FileArray(i) = oFileName
'Get the next file name
oFileName = Dir$
Loop
'Resize and preserve the array
ReDim Preserve FileArray(1 To i)
Application.ScreenUpdating = False
'Provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=C:\TestDataBase.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset,
adLockOptimistic 'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)
vRecordSet.AddNew
With myDoc
If .FormFields("Text1").Result <> "" Then _
vRecordSet!Name = .FormFields("Text1").Result
If .FormFields("Text2").Result <> "" Then _
vRecordSet("Favorite Food") = .FormFields("Text2").Result
If .FormFields("Text3").Result <> "" Then _
vRecordSet("Favorite Color") = .FormFields("Text3").Result
.Close
End With
Next i
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
Application.ScreenUpdating = True
End Sub

 

[Jezebel]

For ADO help, start here: http://support.microsoft.com/ph/683?sid=221, or do
a Google. ADO is part of a larger topic: Microsoft Data Access Components.

Note that SQL is different, and documented separately. It is a published
standard, although there are minor variations in different implementations.
If you Google for SQL +"Insert into" you'll a dozen tutorials.


The specific problem with your code is that you need to quote string
values --

"INSERT INTO [MyTable] ([Test1], [Test2], [Test3]) VALUES ('TestText1',
'TestText2', 'TestText3');"

In practice, quoting the string value means that you also have to check that
the value itself does not contain quotes. Beginners' code tends to fall over
trying to insert a name like "O'Brien". Square brackets are required around
data object names that would otherwise be invalid -- eg containing spaces;
they are optional but not harmful otherwise.

When building strings like this, you might want to build it in sections,
perhaps using a loop to construct the field name and field value clauses,
then package the whole lot together.

Well-formed SQL statements terminate with a semi-colon. Doesn't matter here,
but does when you get to multi-line procedure statements.

Jezebel,

I couldn't locoate a ADO help file at microsoft.support

Do you know where the download can is located or the specific name?

I also cannot get this SQL statement to work. I tied to peel away
everything but just the basics to see if I could get it to work and then
build on it, but I get an error on the .Execute stating "No value given
for one or more required parameters." Can you tell me what "parameter" I
am missing?

Thanks

Sub Testing()
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim pSQL As String
vConnection.ConnectionString = "data source=E:\My
Documents\Batch\TestDataBase2.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
vConnection.Execute "DELETE * FROM MyTable"
pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES(TestText1,
TestText2, TestText3)"
vConnection.Execute pSQL
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
End Sub




--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

How do you know or how can you determine what references are required
to run your code?

Experiment with the object model or check the documentation for the
library you want to use.

Doug's code opens and writes to an existing database. I would prefer
creating a new database and defining the fields in my macro. I think
(I don't know) that if I can figure out how to "create a new"
database vice "open" and existing database that I might be able to
define the structure.

I realize this question might be better suited for an Access group,
but does anyone reading know how to create a new database and table
from VBA in Word.

An Access group might help, but what you're working with here is the
JET database engine. Access uses it, but so do other databases.
Download the ADO helpfile from the Microsoft site and do some
homework Also look at http://www.freevbcode.com/ShowCode.Asp?ID=75
for a sample VB app that does most of the things you'll need.

Creating a new database in code is simple enough in principle, but it
needs a sh*t-load of code to do it. You have to work your way through
defining a) the tables, b) the fields in each table, c) the indexes
for each table, and d) the relationships. Unless you have seriously
pressing reasons for creating the database on the fly, it is *much*
easier to create your database using Access or MySQL, then just use
it in your code.

Doug's code uses "Provider=Microsoft.Jet.OLEDB.4.0;" Doug, and
rocket scientist excluded, how would anyone attempting to write code
with formal training know to use something like that? Is that the
way or one of many ways? If there are others perhaps more
straigtforward I would appreciate seeing a few examples.

It's in the documentation.


In respect of your code, consider whether you should do ALL your data
work entirely with SQL statements, eg instead of using
"vRecordSet.AddNew ....Fields(x) = ... Update" you use something

Dim pSQL as string
pSQL = "INSERT INTO MyTable (Text1, Text2, Text2) VALUES (" &
MyDoc.FormFields("Text1").Result .... & ");"
vConnection.Execute pSQL

It's part of the standard

Some advantages of this approach ---

- It works with any SQL-compliant database. (which is pretty well all
of them)
- Within your application you can separate the database functions
from your main code. Put all the database work (finding, opening,
closing, etc) into a separate class; your main code simply passes the
SQL string to the class. That way your main code doesn't care what
sort of database connection you have, and all your DB errors (there
tend to be a lot when you're just starting out) are all in one place.
You'll also need to add some functions for cleaning up strings,
preparing dates, bracketing table and field names if necessary, etc.
- It's more flexible than hard-coding field names and types.


If you're writing user-entered form fields directly into a database,
you should at least be aware of the risks of SQL-injection.




And a separate point: to check if a string is empty, it's much more
efficient to check if the length is zero [ len(MyString) = 0 ] than
to look at the string content [ MyString = "" ] In VBA, strings are
stored as a header comprising the address and length, and the body.
Checking if the string = "" means retrieving the adderess and length,
using them to retrieve the string itself, then doing a character
comparison. Checking the length means retrieving the header only.

Regulars here know that I have a website and I think that showing a
method for collecting data from Word formfields into an Access
database would be helpful to other users. If it exists out there as
a FAQ or website I dont' know about it and spent the better part of
a day scatching together what I have. I certainly would like hear
your comments and suggestions for improvement.


Sub TallyData4()
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim myDoc As Word.Document

oPath = GetPathToUse
If oPath = "" Then
MsgBox "A folder was not selected"
Exit Sub
End If
'Identify files names
oFileName = Dir$(oPath & "*.doc")
ReDim FileArray(1 To 1000) 'A number larger the expected number of
replies
'Add file name to the array
Do While oFileName <> ""
i = i + 1
FileArray(i) = oFileName
'Get the next file name
oFileName = Dir$
Loop
'Resize and preserve the array
ReDim Preserve FileArray(1 To i)
Application.ScreenUpdating = False
'Provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=C:\TestDataBase.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset,
adLockOptimistic 'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)
vRecordSet.AddNew
With myDoc
If .FormFields("Text1").Result <> "" Then _
vRecordSet!Name = .FormFields("Text1").Result
If .FormFields("Text2").Result <> "" Then _
vRecordSet("Favorite Food") = .FormFields("Text2").Result
If .FormFields("Text3").Result <> "" Then _
vRecordSet("Favorite Color") = .FormFields("Text3").Result
.Close
End With
Next i
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
Application.ScreenUpdating = True
End Sub

 

[Greg Maxey]

Jezebel,

Thanks.

"INSERT INTO [MyTable] ([Test1], [Test2], [Test3]) VALUES ('TestText1',
'TestText2', 'TestText3');"

Yes, I made it that far. The problem is getting the formfield.results to
work.

For example, if I want the field "Test1" to be the value of "Text1" in the
document. I tried to mimic your earlier example, but everything I try
either returns a compile error, a runtime error, or results in the literally
text being placed in the field. How do you write this line so that the
"result" of the formfield is the "value" in the database field?

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES( &
ActiveDocument.FormFields("Text1").Result oTest & , 'TestText2',
'TestText3');'"


--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

For ADO help, start here:
http://support.microsoft.com/ph/683?sid=221, or do a Google. ADO is
part of a larger topic: Microsoft Data Access Components.
Note that SQL is different, and documented separately. It is a
published standard, although there are minor variations in different
implementations. If you Google for SQL +"Insert into" you'll a dozen
tutorials.

The specific problem with your code is that you need to quote string
values --

"INSERT INTO [MyTable] ([Test1], [Test2], [Test3]) VALUES
('TestText1', 'TestText2', 'TestText3');"

In practice, quoting the string value means that you also have to
check that the value itself does not contain quotes. Beginners' code
tends to fall over trying to insert a name like "O'Brien". Square
brackets are required around data object names that would otherwise
be invalid -- eg containing spaces; they are optional but not harmful
otherwise.
When building strings like this, you might want to build it in
sections, perhaps using a loop to construct the field name and field
value clauses, then package the whole lot together.

Well-formed SQL statements terminate with a semi-colon. Doesn't
matter here, but does when you get to multi-line procedure statements.

Jezebel,

I couldn't locoate a ADO help file at microsoft.support

Do you know where the download can is located or the specific name?

I also cannot get this SQL statement to work. I tied to peel away
everything but just the basics to see if I could get it to work and
then build on it, but I get an error on the .Execute stating "No
value given for one or more required parameters." Can you tell me
what "parameter" I am missing?

Thanks

Sub Testing()
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim pSQL As String
vConnection.ConnectionString = "data source=E:\My
Documents\Batch\TestDataBase2.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset,
adLockOptimistic vConnection.Execute "DELETE * FROM MyTable"
pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES(TestText1,
TestText2, TestText3)"
vConnection.Execute pSQL
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
End Sub




--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

How do you know or how can you determine what references are
required to run your code?

Experiment with the object model or check the documentation for the
library you want to use.



Doug's code opens and writes to an existing database. I would
prefer creating a new database and defining the fields in my
macro. I think (I don't know) that if I can figure out how to
"create a new" database vice "open" and existing database that I
might be able to define the structure.

I realize this question might be better suited for an Access group,
but does anyone reading know how to create a new database and table
from VBA in Word.

An Access group might help, but what you're working with here is the
JET database engine. Access uses it, but so do other databases.
Download the ADO helpfile from the Microsoft site and do some
homework Also look at
http://www.freevbcode.com/ShowCode.Asp?ID=75 for a sample VB app
that does most of the things you'll need. Creating a new database in
code is simple enough in principle, but
it needs a sh*t-load of code to do it. You have to work your way
through defining a) the tables, b) the fields in each table, c) the
indexes for each table, and d) the relationships. Unless you have
seriously pressing reasons for creating the database on the fly, it
is *much* easier to create your database using Access or MySQL,
then just use it in your code.


Doug's code uses "Provider=Microsoft.Jet.OLEDB.4.0;" Doug, and
rocket scientist excluded, how would anyone attempting to write
code with formal training know to use something like that? Is
that the way or one of many ways? If there are others perhaps more
straigtforward I would appreciate seeing a few examples.

It's in the documentation.


In respect of your code, consider whether you should do ALL your
data work entirely with SQL statements, eg instead of using
"vRecordSet.AddNew ....Fields(x) = ... Update" you use something

Dim pSQL as string
pSQL = "INSERT INTO MyTable (Text1, Text2, Text2) VALUES (" &
MyDoc.FormFields("Text1").Result .... & ");"
vConnection.Execute pSQL

It's part of the standard

Some advantages of this approach ---

- It works with any SQL-compliant database. (which is pretty well
all of them)
- Within your application you can separate the database functions
from your main code. Put all the database work (finding, opening,
closing, etc) into a separate class; your main code simply passes
the SQL string to the class. That way your main code doesn't care
what sort of database connection you have, and all your DB errors
(there tend to be a lot when you're just starting out) are all in
one place. You'll also need to add some functions for cleaning up
strings, preparing dates, bracketing table and field names if
necessary, etc. - It's more flexible than hard-coding field names
and types. If you're writing user-entered form fields directly into a
database,
you should at least be aware of the risks of SQL-injection.




And a separate point: to check if a string is empty, it's much more
efficient to check if the length is zero [ len(MyString) = 0 ] than
to look at the string content [ MyString = "" ] In VBA, strings are
stored as a header comprising the address and length, and the body.
Checking if the string = "" means retrieving the adderess and
length, using them to retrieve the string itself, then doing a
character comparison. Checking the length means retrieving the
header only.

Regulars here know that I have a website and I think that showing a
method for collecting data from Word formfields into an Access
database would be helpful to other users. If it exists out there
as a FAQ or website I dont' know about it and spent the better
part of a day scatching together what I have. I certainly would
like hear your comments and suggestions for improvement.


Sub TallyData4()
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim myDoc As Word.Document

oPath = GetPathToUse
If oPath = "" Then
MsgBox "A folder was not selected"
Exit Sub
End If
'Identify files names
oFileName = Dir$(oPath & "*.doc")
ReDim FileArray(1 To 1000) 'A number larger the expected number of
replies
'Add file name to the array
Do While oFileName <> ""
i = i + 1
FileArray(i) = oFileName
'Get the next file name
oFileName = Dir$
Loop
'Resize and preserve the array
ReDim Preserve FileArray(1 To i)
Application.ScreenUpdating = False
'Provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=C:\TestDataBase.mdb;"
& _
"Provider=Microsoft.Jet.OLEDB.4.0;" vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset,
adLockOptimistic 'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)
vRecordSet.AddNew
With myDoc
If .FormFields("Text1").Result <> "" Then _
vRecordSet!Name = .FormFields("Text1").Result
If .FormFields("Text2").Result <> "" Then _
vRecordSet("Favorite Food") = .FormFields("Text2").Result
If .FormFields("Text3").Result <> "" Then _
vRecordSet("Favorite Color") = .FormFields("Text3").Result
.Close
End With
Next i
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
Application.ScreenUpdating = True
End Sub

 

[Jezebel]

You're still missing some quotes from the final SQL string. Try

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES(' " &
ActiveDocument.FormFields("Text1").Result & " ', 'TestText2',
'TestText3');'"

I've added some spaces around the quotes so you can see them; you don't want
them in your real code.



I found it worth creating a function that returned the string argument with
the quotes attached:

Private Function QuoteString(MyString as string) as string
QuoteString = " ' " & MyString & " ' "
End Function

(actually the function also checks for and deals with quote characters in
the argument).

Then use

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES(" &
QuoteString(ActiveDocument.FormFields("Text1").Result ) & " ...

Jezebel,

Thanks.

"INSERT INTO [MyTable] ([Test1], [Test2], [Test3]) VALUES ('TestText1',
'TestText2', 'TestText3');"

Yes, I made it that far. The problem is getting the formfield.results to
work.

For example, if I want the field "Test1" to be the value of "Text1" in
the document. I tried to mimic your earlier example, but everything I try
either returns a compile error, a runtime error, or results in the
literally text being placed in the field. How do you write this line so
that the "result" of the formfield is the "value" in the database field?

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES( &
ActiveDocument.FormFields("Text1").Result oTest & , 'TestText2',
'TestText3');'"


--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

For ADO help, start here:
http://support.microsoft.com/ph/683?sid=221, or do a Google. ADO is
part of a larger topic: Microsoft Data Access Components.
Note that SQL is different, and documented separately. It is a
published standard, although there are minor variations in different
implementations. If you Google for SQL +"Insert into" you'll a dozen
tutorials.

The specific problem with your code is that you need to quote string
values --

"INSERT INTO [MyTable] ([Test1], [Test2], [Test3]) VALUES
('TestText1', 'TestText2', 'TestText3');"

In practice, quoting the string value means that you also have to
check that the value itself does not contain quotes. Beginners' code
tends to fall over trying to insert a name like "O'Brien". Square
brackets are required around data object names that would otherwise
be invalid -- eg containing spaces; they are optional but not harmful
otherwise.
When building strings like this, you might want to build it in
sections, perhaps using a loop to construct the field name and field
value clauses, then package the whole lot together.

Well-formed SQL statements terminate with a semi-colon. Doesn't
matter here, but does when you get to multi-line procedure statements.

Jezebel,

I couldn't locoate a ADO help file at microsoft.support

Do you know where the download can is located or the specific name?

I also cannot get this SQL statement to work. I tied to peel away
everything but just the basics to see if I could get it to work and
then build on it, but I get an error on the .Execute stating "No
value given for one or more required parameters." Can you tell me
what "parameter" I am missing?

Thanks

Sub Testing()
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim pSQL As String
vConnection.ConnectionString = "data source=E:\My
Documents\Batch\TestDataBase2.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset,
adLockOptimistic vConnection.Execute "DELETE * FROM MyTable"
pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES(TestText1,
TestText2, TestText3)"
vConnection.Execute pSQL
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
End Sub




--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.


Jezebel wrote:
How do you know or how can you determine what references are
required to run your code?

Experiment with the object model or check the documentation for the
library you want to use.



Doug's code opens and writes to an existing database. I would
prefer creating a new database and defining the fields in my
macro. I think (I don't know) that if I can figure out how to
"create a new" database vice "open" and existing database that I
might be able to define the structure.

I realize this question might be better suited for an Access group,
but does anyone reading know how to create a new database and table
from VBA in Word.

An Access group might help, but what you're working with here is the
JET database engine. Access uses it, but so do other databases.
Download the ADO helpfile from the Microsoft site and do some
homework Also look at
http://www.freevbcode.com/ShowCode.Asp?ID=75 for a sample VB app
that does most of the things you'll need. Creating a new database in
code is simple enough in principle, but
it needs a sh*t-load of code to do it. You have to work your way
through defining a) the tables, b) the fields in each table, c) the
indexes for each table, and d) the relationships. Unless you have
seriously pressing reasons for creating the database on the fly, it
is *much* easier to create your database using Access or MySQL,
then just use it in your code.


Doug's code uses "Provider=Microsoft.Jet.OLEDB.4.0;" Doug, and
rocket scientist excluded, how would anyone attempting to write
code with formal training know to use something like that? Is
that the way or one of many ways? If there are others perhaps more
straigtforward I would appreciate seeing a few examples.

It's in the documentation.


In respect of your code, consider whether you should do ALL your
data work entirely with SQL statements, eg instead of using
"vRecordSet.AddNew ....Fields(x) = ... Update" you use something

Dim pSQL as string
pSQL = "INSERT INTO MyTable (Text1, Text2, Text2) VALUES (" &
MyDoc.FormFields("Text1").Result .... & ");"
vConnection.Execute pSQL

It's part of the standard

Some advantages of this approach ---

- It works with any SQL-compliant database. (which is pretty well
all of them)
- Within your application you can separate the database functions
from your main code. Put all the database work (finding, opening,
closing, etc) into a separate class; your main code simply passes
the SQL string to the class. That way your main code doesn't care
what sort of database connection you have, and all your DB errors
(there tend to be a lot when you're just starting out) are all in
one place. You'll also need to add some functions for cleaning up
strings, preparing dates, bracketing table and field names if
necessary, etc. - It's more flexible than hard-coding field names
and types. If you're writing user-entered form fields directly into a
database,
you should at least be aware of the risks of SQL-injection.




And a separate point: to check if a string is empty, it's much more
efficient to check if the length is zero [ len(MyString) = 0 ] than
to look at the string content [ MyString = "" ] In VBA, strings are
stored as a header comprising the address and length, and the body.
Checking if the string = "" means retrieving the adderess and
length, using them to retrieve the string itself, then doing a
character comparison. Checking the length means retrieving the
header only.

Regulars here know that I have a website and I think that showing a
method for collecting data from Word formfields into an Access
database would be helpful to other users. If it exists out there
as a FAQ or website I dont' know about it and spent the better
part of a day scatching together what I have. I certainly would
like hear your comments and suggestions for improvement.


Sub TallyData4()
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim myDoc As Word.Document

oPath = GetPathToUse
If oPath = "" Then
MsgBox "A folder was not selected"
Exit Sub
End If
'Identify files names
oFileName = Dir$(oPath & "*.doc")
ReDim FileArray(1 To 1000) 'A number larger the expected number of
replies
'Add file name to the array
Do While oFileName <> ""
i = i + 1
FileArray(i) = oFileName
'Get the next file name
oFileName = Dir$
Loop
'Resize and preserve the array
ReDim Preserve FileArray(1 To i)
Application.ScreenUpdating = False
'Provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=C:\TestDataBase.mdb;"
& _
"Provider=Microsoft.Jet.OLEDB.4.0;" vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset,
adLockOptimistic 'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)
vRecordSet.AddNew
With myDoc
If .FormFields("Text1").Result <> "" Then _
vRecordSet!Name = .FormFields("Text1").Result
If .FormFields("Text2").Result <> "" Then _
vRecordSet("Favorite Food") = .FormFields("Text2").Result
If .FormFields("Text3").Result <> "" Then _
vRecordSet("Favorite Color") = .FormFields("Text3").Result
.Close
End With
Next i
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
Application.ScreenUpdating = True
End Sub

 

[Greg Maxey]

Jezebel,

Right. I had asked a similiar question in the Access group and a
couple of helpful chaps there sorted me out. I will give your Function
a go this evening.

I don't know if I have the brain cells left to try to master Access and
this SQL stuff, but it has been an interesting exercise. I may stick
closer to Word where at least the corners are padded.

Thanks for all the prodding and help.

 

[Greg Maxey]

Jezebel,

Got it all worked out. Thanks.

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

You're still missing some quotes from the final SQL string. Try

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES(' " &
ActiveDocument.FormFields("Text1").Result & " ', 'TestText2',
'TestText3');'"

I've added some spaces around the quotes so you can see them; you
don't want them in your real code.



I found it worth creating a function that returned the string
argument with the quotes attached:

Private Function QuoteString(MyString as string) as string
QuoteString = " ' " & MyString & " ' "
End Function

(actually the function also checks for and deals with quote
characters in the argument).

Then use

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES(" &
QuoteString(ActiveDocument.FormFields("Text1").Result ) & " ...

Jezebel,

Thanks.

"INSERT INTO [MyTable] ([Test1], [Test2], [Test3]) VALUES
('TestText1', 'TestText2', 'TestText3');"

Yes, I made it that far. The problem is getting the
formfield.results to work.

For example, if I want the field "Test1" to be the value of "Text1"
in the document. I tried to mimic your earlier example, but
everything I try either returns a compile error, a runtime error, or
results in the literally text being placed in the field. How do you
write this line so that the "result" of the formfield is the "value"
in the database field? pSQL = "INSERT INTO MyTable(Test1, Test2, Test3)
VALUES( &
ActiveDocument.FormFields("Text1").Result oTest & , 'TestText2',
'TestText3');'"


--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

For ADO help, start here:
http://support.microsoft.com/ph/683?sid=221, or do a Google. ADO is
part of a larger topic: Microsoft Data Access Components.
Note that SQL is different, and documented separately. It is a
published standard, although there are minor variations in different
implementations. If you Google for SQL +"Insert into" you'll a dozen
tutorials.

The specific problem with your code is that you need to quote string
values --

"INSERT INTO [MyTable] ([Test1], [Test2], [Test3]) VALUES
('TestText1', 'TestText2', 'TestText3');"

In practice, quoting the string value means that you also have to
check that the value itself does not contain quotes. Beginners' code
tends to fall over trying to insert a name like "O'Brien". Square
brackets are required around data object names that would otherwise
be invalid -- eg containing spaces; they are optional but not
harmful otherwise.
When building strings like this, you might want to build it in
sections, perhaps using a loop to construct the field name and field
value clauses, then package the whole lot together.

Well-formed SQL statements terminate with a semi-colon. Doesn't
matter here, but does when you get to multi-line procedure
Jezebel,

I couldn't locoate a ADO help file at microsoft.support

Do you know where the download can is located or the specific name?

I also cannot get this SQL statement to work. I tied to peel away
everything but just the basics to see if I could get it to work and
then build on it, but I get an error on the .Execute stating "No
value given for one or more required parameters." Can you tell me
what "parameter" I am missing?

Thanks

Sub Testing()
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim pSQL As String
vConnection.ConnectionString = "data source=E:\My
Documents\Batch\TestDataBase2.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset,
adLockOptimistic vConnection.Execute "DELETE * FROM MyTable"
pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES(TestText1,
TestText2, TestText3)"
vConnection.Execute pSQL
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
End Sub




--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.


Jezebel wrote:
How do you know or how can you determine what references are
required to run your code?

Experiment with the object model or check the documentation for
the library you want to use.



Doug's code opens and writes to an existing database. I would
prefer creating a new database and defining the fields in my
macro. I think (I don't know) that if I can figure out how to
"create a new" database vice "open" and existing database that I
might be able to define the structure.

I realize this question might be better suited for an Access
group, but does anyone reading know how to create a new database
and table from VBA in Word.

An Access group might help, but what you're working with here is
the JET database engine. Access uses it, but so do other
databases. Download the ADO helpfile from the Microsoft site and
do some homework Also look at
http://www.freevbcode.com/ShowCode.Asp?ID=75 for a sample VB app
that does most of the things you'll need. Creating a new database
in code is simple enough in principle, but
it needs a sh*t-load of code to do it. You have to work your way
through defining a) the tables, b) the fields in each table, c)
the indexes for each table, and d) the relationships. Unless you
have seriously pressing reasons for creating the database on the
fly, it is *much* easier to create your database using Access or
MySQL, then just use it in your code.


Doug's code uses "Provider=Microsoft.Jet.OLEDB.4.0;" Doug, and
rocket scientist excluded, how would anyone attempting to write
code with formal training know to use something like that? Is
that the way or one of many ways? If there are others perhaps
more straigtforward I would appreciate seeing a few examples.

It's in the documentation.


In respect of your code, consider whether you should do ALL your
data work entirely with SQL statements, eg instead of using
"vRecordSet.AddNew ....Fields(x) = ... Update" you use something

Dim pSQL as string
pSQL = "INSERT INTO MyTable (Text1, Text2, Text2) VALUES (" &
MyDoc.FormFields("Text1").Result .... & ");"
vConnection.Execute pSQL

It's part of the standard

Some advantages of this approach ---

- It works with any SQL-compliant database. (which is pretty well
all of them)
- Within your application you can separate the database functions
from your main code. Put all the database work (finding, opening,
closing, etc) into a separate class; your main code simply passes
the SQL string to the class. That way your main code doesn't care
what sort of database connection you have, and all your DB errors
(there tend to be a lot when you're just starting out) are all in
one place. You'll also need to add some functions for cleaning up
strings, preparing dates, bracketing table and field names if
necessary, etc. - It's more flexible than hard-coding field names
and types. If you're writing user-entered form fields directly
into a database,
you should at least be aware of the risks of SQL-injection.




And a separate point: to check if a string is empty, it's much
more efficient to check if the length is zero [ len(MyString) = 0
] than to look at the string content [ MyString = "" ] In VBA,
strings are stored as a header comprising the address and length,
and the body. Checking if the string = "" means retrieving the
adderess and length, using them to retrieve the string itself,
then doing a character comparison. Checking the length means
retrieving the header only.

Regulars here know that I have a website and I think that
showing a method for collecting data from Word formfields into
an Access database would be helpful to other users. If it
exists out there as a FAQ or website I dont' know about it and
spent the better part of a day scatching together what I have. I
certainly would like hear your comments and suggestions for
improvement. Sub TallyData4()
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim myDoc As Word.Document

oPath = GetPathToUse
If oPath = "" Then
MsgBox "A folder was not selected"
Exit Sub
End If
'Identify files names
oFileName = Dir$(oPath & "*.doc")
ReDim FileArray(1 To 1000) 'A number larger the expected number
of replies
'Add file name to the array
Do While oFileName <> ""
i = i + 1
FileArray(i) = oFileName
'Get the next file name
oFileName = Dir$
Loop
'Resize and preserve the array
ReDim Preserve FileArray(1 To i)
Application.ScreenUpdating = False
'Provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=C:\TestDataBase.mdb;"
& _
"Provider=Microsoft.Jet.OLEDB.4.0;" vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset,
adLockOptimistic 'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)
vRecordSet.AddNew
With myDoc
If .FormFields("Text1").Result <> "" Then _
vRecordSet!Name = .FormFields("Text1").Result
If .FormFields("Text2").Result <> "" Then _
vRecordSet("Favorite Food") = .FormFields("Text2").Result
If .FormFields("Text3").Result <> "" Then _
vRecordSet("Favorite Color") = .FormFields("Text3").Result
.Close
End With
Next i
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
Application.ScreenUpdating = True
End Sub