|
|
|
date: Tue, 19 Aug 2008 11:10:02 -0700,
group: microsoft.public.word.vba.general
back
Exporting to Access Help
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
--
Eric the Rookie
date: Tue, 19 Aug 2008 11:10:02 -0700
author: Eric
Re: Exporting to Access Help
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" wrote in message
news:1DD8C1E6-3280-413B-96AC-CEC578D2643F@microsoft.com...
>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
>
>
> --
> Eric the Rookie
date: Wed, 20 Aug 2008 20:39:34 +1000
author: Doug Robbins - Word MVP
Re: Exporting to Access Help
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 the Rookie
"Doug Robbins - Word MVP" wrote:
> 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" wrote in message
> news:1DD8C1E6-3280-413B-96AC-CEC578D2643F@microsoft.com...
> >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
> >
> >
> > --
> > Eric the Rookie
>
>
>
date: Wed, 20 Aug 2008 05:44:01 -0700
author: Eric
Re: Exporting to Access Help
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
--
Eric the Rookie
"Eric" wrote:
> 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 the Rookie
>
>
> "Doug Robbins - Word MVP" wrote:
>
> > 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" wrote in message
> > news:1DD8C1E6-3280-413B-96AC-CEC578D2643F@microsoft.com...
> > >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
> > >
> > >
> > > --
> > > Eric the Rookie
> >
> >
> >
date: Wed, 20 Aug 2008 06:38:01 -0700
author: Eric
Re: Exporting to Access Help
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" wrote in message
news:88DB04A2-CB98-454D-A1BF-023186D62B7F@microsoft.com...
> 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
>
> --
> Eric the Rookie
>
>
> "Eric" wrote:
>
>> 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 the Rookie
>>
>>
>> "Doug Robbins - Word MVP" wrote:
>>
>> > 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" wrote in message
>> > news:1DD8C1E6-3280-413B-96AC-CEC578D2643F@microsoft.com...
>> > >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
>> > >
>> > >
>> > > --
>> > > Eric the Rookie
>> >
>> >
>> >
date: Sat, 23 Aug 2008 06:57:16 +1000
author: Doug Robbins - Word MVP
Re: Exporting to Access Help
Thanks again Doug..... All tables are now being populated.
Have a great weekend!
--
Eric the Rookie
"Doug Robbins - Word MVP" wrote:
> 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" wrote in message
> news:88DB04A2-CB98-454D-A1BF-023186D62B7F@microsoft.com...
> > 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
> >
> > --
> > Eric the Rookie
> >
> >
> > "Eric" wrote:
> >
> >> 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 the Rookie
> >>
> >>
> >> "Doug Robbins - Word MVP" wrote:
> >>
> >> > 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" wrote in message
> >> > news:1DD8C1E6-3280-413B-96AC-CEC578D2643F@microsoft.com...
> >> > >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
> >> > >
> >> > >
> >> > > --
> >> > > Eric the Rookie
> >> >
> >> >
> >> >
>
>
>
date: Fri, 22 Aug 2008 20:25:01 -0700
author: Eric
|
|
