Re: Establish connection with and transferring data to Access

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.


Jezebel wrote:
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.


"Greg Maxey" <gmaxey@xxxxxxxxxxxxxxxxxxx> wrote in message
news:OuTwhr2XGHA.3444@xxxxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • RE: Combo Box on User Form
    ... 'declare variables for new connection and recordset and declare variables ... Dim vConnection As New ADODB.Connection ... Dim vClientFName As String ... MsgBox "The connection to this database is working!", ...
    (microsoft.public.word.vba.general)
  • Re: running fRefreshLinks from another database
    ... lock the database I want to update links in. ... Dim i As Integer, strDBPath As String, strTbl As String ... Dim dbCurr As Database, dbLink As Database ...
    (microsoft.public.access.modulesdaovba)
  • Re: Combo Box on User Form
    ... 'declare variables for new connection and recordset and declare variables ... Dim vConnection As New ADODB.Connection ... Dim vClientFName As String ... MsgBox "The connection to this database is working!", ...
    (microsoft.public.word.vba.general)
  • Re: Changing the background color and re-linking the backend when
    ... Essentially what I needed was to replace every reference to tblMember to be replaced to tblMemberUS in one Access database and to tblMemberNU in another Access database. ... Dim strDbName As String ... Function RefreshLinksBeSef() As Boolean ...
    (microsoft.public.access.formscoding)
  • Re: A VB 6 program to read from and save to text file
    ... I wrote a small program which for you, which imports the data from your text file into a database and displays them in a DBGrid. ... Const OldFile As String = "Datafile.txt.old" ... Dim MyData As String ... Kill MyPath & OldFile ...
    (microsoft.public.vb.winapi)
Loading