Exporting to Access Help

[Eric]

I have a template that has 100 form fields, Scary. I am using the code
below to export the data in the form fields to an Access Database (Flat
Table) I then run Union Queries to extract from the Flat table into
normalized tables. this has been working great. But now I am finding out
that when the record (100 fields) are exported to only one table....... the
record size is to large for Access.

The code below exports all fields to one table. I need to break up this
export into say 5 different export functions. Each function will pull
specific form fields and export them to a specific table in access. then I
could run the 5 Export codes one after the other.

I'm not sure as to how to list the specific fields I want to export...... or
........ how to identify the specific tabel in Access I want to hit.

Any help on this would really be great, thanks in advance. Here is the code
that works fine, but I need to break it up into say 5 different macros.

Sub Export()
'
'
'
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim SH As Shell32.Shell
Dim Fldr As Shell32.Folder
Dim FldrPath As String
Dim RecordDoc As String
Dim dsource As String
Dim Source As Document
Dim i As Long, j As Long
Dim FileToKill As String

'Get the folder where the forms have been saved.

Set SH = New Shell32.Shell
Set Fldr = SH.BrowseForFolder(0, "Select the Directory (Folder) that
contains your Review Report", &H400)
If Not Fldr Is Nothing Then
FldrPath = Fldr.Items.Item.Path & "\"
End If
Set Fldr = Nothing
RecordDoc = Dir$(FldrPath & "*.doc")

With Dialogs(wdDialogFileOpen)
MsgBox "Locate the Review Tracker Database and select it"
If .Display <> -1 Then
dsource = ""
Else
dsource = WordBasic.FileNameInfo$(.Name, 1)
End If
End With
' Make sure the user selected an Access database
If Right(dsource, 3) <> "mdb" Then
MsgBox "You did not select a valid Access Database file type (.mdb) Review
Tracker. Locate the Review Tracker Database....and select it to proceed."
Exit Sub
Else
dsource = dsource & ";"
End If

vConnection.ConnectionString = "data source=" & dsource & _
"Provider=Microsoft.Jet.OLEDB.4.0;"

vConnection.Open

vRecordSet.Open "Review", vConnection, adOpenKeyset, adLockOptimistic

i = 0
While RecordDoc <> ""
vRecordSet.AddNew
Set Source = Documents.Open(FldrPath & RecordDoc)
With Source
For j = 1 To vRecordSet.Fields.Count
If .Bookmarks.Exists(vRecordSet.Fields(j - 1).Name) Then
If .FormFields(vRecordSet.Fields(j - 1).Name).Result <> ""
Then
vRecordSet(vRecordSet.Fields(j - 1).Name) = _
.FormFields(vRecordSet.Fields(j - 1).Name).Result
End If
End If
Next j
End With
vRecordSet.Update
i = i + 1
FileToKill = Source.FullName
Source.SaveAs FldrPath & "Processed\" & Source.Name
Source.Close wdDoNotSaveChanges
Kill FileToKill
RecordDoc = Dir
Wend

MsgBox i & " Records Added."

vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing

End Sub

 

[Doug Robbins - Word MVP]

If you had two tables in the database - Review and Review1, you should be
able to do it using the following:

i = 0
While RecordDoc <> ""
vRecordSet.Open "Review", vConnection, adOpenKeyset, adLockOptimistic
vRecordSet.AddNew
Set Source = Documents.Open(FldrPath & RecordDoc)
With Source
For j = 1 To vRecordSet.Fields.Count
If .Bookmarks.Exists(vRecordSet.Fields(j - 1).Name) Then
If .FormFields(vRecordSet.Fields(j - 1).Name).Result <> ""
Then
vRecordSet(vRecordSet.Fields(j - 1).Name) = _
.FormFields(vRecordSet.Fields(j - 1).Name).Result
End If
End If
Next j
End With
vRecordSet.Update
vRecordSet.Close
vRecordSet.Open "Review1", vConnection, adOpenKeyset, adLockOptimistic
vRecordSet.AddNew
Set Source = Documents.Open(FldrPath & RecordDoc)
With Source
For j = 1 To vRecordSet.Fields.Count
If .Bookmarks.Exists(vRecordSet.Fields(j - 1).Name) Then
If .FormFields(vRecordSet.Fields(j - 1).Name).Result <> ""
Then
vRecordSet(vRecordSet.Fields(j - 1).Name) = _
.FormFields(vRecordSet.Fields(j - 1).Name).Result
End If
End If
Next j
End With
vRecordSet.Update
vRecordSet.Close
i = i + 1
FileToKill = Source.FullName
Source.SaveAs FldrPath & "Processed\" & Source.Name
Source.Close wdDoNotSaveChanges
Kill FileToKill
RecordDoc = Dir
Wend

Repeat the block of code starting with vRecordSet.Open... and ending with
vRecordSet.Close for as many tables as you need to use.

The formfield data that is exported to an individual table is only the data
from those formfields that have the same bookmark names as the fields in
that table.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Eric]

Hi Doug, Thanks for the response. You where instrumental in helping me with
this code the first time around.

The only thing I may be missing is: Where in the code do I indicate or list
the Fields that need to be exported to the selected table?


Thanks

 

[Eric]

Doug, maybe I missed your last sentence.... Sorry.

So I don't need to identify the specific fields to export. "Only the data
from those formfields that have the same bookmark names as the fields in the
identified table" will be exported.

Do I have that right. So merely identifing the tables only (as your code
shows) will take care of it?

Thanks

 

[Doug Robbins - Word MVP]

That's correct. The code iterates through the fields in the Access table
and using the

If .Bookmarks.Exists(vRecordSet.Fields(j - 1).Name)

it checks for the existence of a formfield for which the bookmark name
corresponds to the field in the Access table and if it does, and there is a
..Result in that formfield, it inserts the .Result into the Access table.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Eric]

Thanks again Doug..... All tables are now being populated.

Have a great weekend!