Feeback\Review

[gmaxey]

My name is Greg Maxey. I'm am dabbler in Office automation and occassionally, the need arises when I want to work with Access. When it does, I usually cringe, because I really know nothing about Access or databases in general.

However, I am reasonably adept at searching Google or ohter forums to find what others have done and then adapt that to my needs. Such is the case now.

I have a MS Word document that contains several content controls. My project involves the following requirements:

1. Extract the data from each control and write it to a field in a *.mdb format database and a *.accdb format database.

2. If the database file doesn't exist then create it.

3. If the target table (terminology guess) doesn't exist then create it.

4. If the target table column count doesn't match the content control count in the document then modify target table.

5. Write content of each content control to the two data bases.

Here is my code. It works. My purpose for posting here is to get feedback/comments on the methods that I used. Are there better ways to do some of the things I've done? For exmple, the process I used to determine column count seems a bit convoluted, but I don't know a better or for that matter a poorer way either. That is all I know.

Thanks. There is also a link to a public copy of my file after the code here:

Option Explicit
Private objConnection As Object
Private strConnectionString As String
Private oCC As ContentControls
Private strCC_Name As String
Private strDBPath As String
'Add Reference to Microsoft ActiveX Data Objects 2.x Library
Sub Demonstration()
WriteFormDataToAccessDataBase 1, "Data"
WriteFormDataToAccessDataBase 2, "Data"
Beep
Application.StatusBar = "Processing complete"
lbl_Exit:
Exit Sub
End Sub
Sub WriteFormDataToAccessDataBase(lngDBFormat As Long, strTable_Name As String)
Dim strField_Headings As String
Dim strField_Values As String
Dim strValue As String
Dim oCC As ContentControls
Dim rsTable As ADODB.Recordset
Dim rsColumns As ADODB.Recordset
Dim lngColumns As Long
Dim lngIndex As Long
Dim strCC_Data As String
Dim oCCPsuedo As Object
'Initialize values
strField_Headings = ""
strField_Values = ""
strValue = ""
If lngDBFormat = 1 Then
strDBPath = "D:\Data Stores\FormData.accdb"
Else
strDBPath = "D:\Data Stores\FormData.mdb"
End If
Set oCC = ActiveDocument.ContentControls
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
Set objConnection = New ADODB.Connection
With objConnection
On Error GoTo Err_Connect
.Open strConnectionString
'Create table record set.
Set rsTable = .OpenSchema(adSchemaTables, Array(Empty, Empty, strTable_Name))
'If tables doesn't exist, then create it.
If rsTable.BOF And rsTable.EOF Then
CreateAccessTable strTable_Name
Set rsTable = Nothing
'Refresh table record set.
Set rsTable = .OpenSchema(adSchemaTables, Array(Empty, Empty, strTable_Name))
End If
'Create columns record set.
Set rsColumns = .OpenSchema(adSchemaColumns, Array(Empty, Empty, "" & rsTable("TABLE_NAME")))
'Count columns in table.
Do While Not rsColumns.EOF
lngColumns = lngColumns + 1
rsColumns.MoveNext
Loop
rsTable.Close
rsColumns.Close

'Is there a column in the DB for each CC in the document?
If lngColumns <> oCC.Count Then
'If not then suspend error handling and modify table.
On Error GoTo 0
ModifyAccessTable strTable_Name
End If
On Error GoTo Err_Connect
'Extract the document content control data.
For lngIndex = 1 To oCC.Count
strCC_Name = oCC(lngIndex).Title
'If the content control has no name, create one.
If strCC_Name = "" Then strCC_Name = "CControl" & lngIndex
'Adapt string to acount for any spaces.
If InStr(strCC_Name, " ") > 0 Then strCC_Name = "[" & strCC_Name & "]"
'Get field data.
Select Case oCC(lngIndex).Type
Case 0, 1, 3, 4, 6
'Rich text, plain text, combobox, dropdown list, date type
strCC_Data = oCC(lngIndex).Range
If strCC_Data = "" Then strCC_Data = " "
Case 2, 5
'picture and group type are assigned default string value.
strCC_Data = " "
Case 8
'Checkbox type.
Set oCCPsuedo = oCC(lngIndex)
If oCCPsuedo.Checked Then
strCC_Data = True
Else
strCC_Data = False
End If
End Select
'Build SQL statement.
Select Case lngIndex
Case Is = oCC.Count
strField_Headings = strField_Headings & strCC_Name
strField_Values = strField_Values & "'" & strCC_Data & "'"
Case Else
strField_Headings = strField_Headings & strCC_Name & ", "
strField_Values = strField_Values & "'" & strCC_Data & "'" & ", "
End Select
Next lngIndex
'Write the data to the database.
strValue = "INSERT INTO " & strTable_Name & " (" & strField_Headings & ") VALUES (" & strField_Values & ")"
.Execute strValue
End With
'Clean up.
Set objConnection = Nothing
Set rsTable = Nothing
Set oCC = Nothing
Set oCCPsuedo = Nothing
lbl_Exit:
Exit Sub
Err_Connect:
Select Case Err.Number
Case Is = -2147467259
If lngDBFormat = 1 Then
CreateAccessDatabase "D:\Data Stores\FormData.accdb"
Else
CreateAccessDatabase "D:\Data Stores\FormData.mdb"
End If
Resume
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
End Sub
Public Sub CreateAccessDatabase(strDBPath As String)
'Creates the Access database file.
Dim objCatalog As Object

Set objCatalog = CreateObject("ADOX.Catalog")
objCatalog.CREATE strConnectionString
'Clean up.
Set objCatalog = Nothing
lbl_Exit:
Exit Sub
End Sub
Sub CreateAccessTable(strName As String)
'Defines a table in the database.
Dim lngIndex As Long
Dim strAdd_Column As String
Set oCC = ActiveDocument.ContentControls
With objConnection
.Execute "CREATE TABLE " & strName
For lngIndex = 1 To oCC.Count
strCC_Name = oCC(lngIndex).Title
'If the content control has no name, create one
If strCC_Name = "" Then strCC_Name = "CControl" & lngIndex
strAdd_Column = "ALTER TABLE " & strName & " ADD COLUMN [" & strCC_Name & "] TEXT;"
.Execute strAdd_Column
Next lngIndex
End With
'Clean up
Set oCC = Nothing
lbl_Exit:
Exit Sub
End Sub
Sub ModifyAccessTable(strName As String)
'Modifies the table in the database.
Dim lngIndex As Long
Dim strAdd_Column As String
Set oCC = ActiveDocument.ContentControls
With objConnection
'.Execute "CREATE TABLE " & strName
For lngIndex = 1 To oCC.Count
strCC_Name = oCC(lngIndex).Title
'If the content control has no name, create one
If strCC_Name = "" Then strCC_Name = "CControl" & lngIndex
On Error GoTo Err_AddingColumn
strAdd_Column = "ALTER TABLE " & strName & " ADD COLUMN [" & strCC_Name & "] TEXT;"
.Execute strAdd_Column
On Error GoTo 0
Err_AddingColumn_Reentry:
Next lngIndex
End With
'Clean up
Set oCC = Nothing
Exit Sub
Err_AddingColumn:
Select Case Err.Number
Case Is = -2147217887
'Column with this name already exists.
Resume Err_AddingColumn_Reentry
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
End Sub

https://dl.dropbox.com/u/64545773/Write%20Form%20Data%20to%20Access.docm

[hansup]

Your connection string will not work for an ACCDB format database.

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

The Jet provider can only work for the older MDB format databases. For ACCDB, you need the ACE provider. Here is the provider piece of a connection string I'm using in an Access 2007 procedure:

Microsoft.ACE.OLEDB.12.0

You can find more detailed information at connectionstrings.com

[gmaxey]

hansup,

I don't certainly don't mean to be argumentative, but what do you mean by "will not work." When I run the code as is, the result is two new access database files. One has a the accdb extension and the other the mdb.

Like I said, I know very little about Access so I probably missing the obvious.

Thanks.

Ok, after posting the reply above, I realized that while the file names were *.accdb and *.mdb, the format of both was Access 2003. I've made the change as you suggested and now have two database where one is a 2003 format and the other a 2010 format.

Thank you!!

[hansup]

I suspect what you've done is created an MDB format database, but given it an .accdb file extension. The file extension does not determine the database format. Double-check my hunch.

Open D:\Data Stores\FormData.accdb in Access. Then in the Immediate Window, check CurrentDb.Version.

Here is what I get with an MDB format database from Access 2007.

? CurrentDb.Version
4.0

And this with an ACCDB format database:

? CurrentDb.Version
12.0

[gmaxey]

hansup,

Our posts are crossing ;-). You were exactly correct and I noticed that shortly after the earlier reply. I must have been updating that reply when you were typing yours.

That part is all sorted out. If you see anymore issue or just general comments the please fire back and you can be brutal. I would like to learn to do it the right way.

[hansup]

Our posts are crossing ;-). You were exactly correct and I noticed that shortly after the earlier reply. I must have been updating that reply when you were typing yours.

Yes, the timing amused me. I yanked your chain for calling me hansug, but after posting found that you'd changed me from ug to up. So I had to edit my post. :giggle

I skimmed your code very briefly and the Provider issue was the only thing which jumped out at me. But why do you want to handle both MDB and ACCDB format database files? Access application versions 2007 through 2013 are perfectly happy to use MDB format. Unless I have a need for ACCDB-specific features, I stick with MDB format ... which means I almost never use ACCDB.

But it's your application, so naturally you get to choose. :whistle:

[gmaxey]

If I spend much time in this forum I will become legendary for typos. I do try to get names right, but even that is taxing at times. Sorry.

The answer to your question "Why both types," is simple ignorance. Until now I just assumed that if a person was working with Access > 2003 then they would want a > 2007 format file. Secondarily, I am just trying to see what I can and can't do.

You've been very helpful. Thanks again.