Is this similar to
using an ADO recordset as a datasource (if it could be
done)?
No. It's just a possible way of overcoming the SQL length constraint in
OpenDataSource.
I would advise that you proceed with caution as
a. I'm not an ADO/ADOX expert. In particular, I know little about ADOX's
understanding of what constitutes a "Procedure" or a "View".
b. ADOX lets you change most features of a Jet database's structure.
c. there are various compatibility problems with ADOX whereby views etc.
created in ADOX do not appear in the list of queries in Access 2000, which
means that you will not be able to see them in a database you open using the
DDE connection method. I believe you may have to use DAO instead of ADO to
create queries that can be seen by Access 2000.
Anyway, below are two sample routines, - the first uses ADOX to create a new
..mdb, and the second creates a table and a primary index, inserts a few
records, then creates a View containing a UNION query.
The rest is up to you, but it may be worth pointing out that there is some
syntax that allows Jet queries to access tables and queries in other
databases, and/or you can create links in Jet to other tables, so if you do
need to avoid altering the source database structure, you may be able to do
everything you need in a "front-end" database that just contains links and
queries. But in all cases you need to test that Word can actually link
successfully to your data.
Sub makedb()
' sample routine to make a Jet database
Dim oCatalog As ADOX.Catalog
Dim sDatabasePath As String
sDatabasePath = "c:\a\adoxtest.mdb"
On Error GoTo DatabaseCreateError
Set oCatalog = New ADOX.Catalog
oCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDatabasePath
On Error GoTo 0
Set oCatalog = Nothing
Exit Sub
DatabaseCreateError:
If Err.Number = -2147217897 Then
Err.Clear
Kill pathname:=sDatabasePath
Resume
Else
' output some rudimentary error info.
Debug.Print Err.Number, Err.Description
End If
End Sub
Sub maketableandquery()
' creates a simple table with a primary key
' in an existing database and inserts a few records
' assumes the database already exists but the table,
' index and query do not
Dim bDeleted As Boolean
Dim oCatalog As ADOX.Catalog
Dim oTable As ADOX.Table
Dim oIndex As ADOX.Index
Dim oCommand As ADODB.Command
Dim oProcedure As ADOX.Procedure
Dim oView As ADOX.View
Dim sDatabasePath As String
Dim sConnectString As String
Dim sTableName As String
Dim sKeyColumnName As String
Dim sQueryName As String
Dim sCommandText As String
sDatabasePath = "c:\a\adoxtest.mdb"
sConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDatabasePath
sTableName = "mytable"
sKeyColumnName = "myID"
sQueryName = "myquery"
On Error GoTo GeneralError
' Create the catalog object and connect it to the database
Set oCatalog = New ADOX.Catalog
oCatalog.ActiveConnection = sConnectString
' Create the table object
Set oTable = New ADOX.Table
' fill in some properties and create some columns
With oTable
.Name = sTableName
Set .ParentCatalog = oCatalog
.Columns.Append sKeyColumnName, adInteger
.Columns.Append "myText", adVarWChar, 50
.Columns.Append "myDate", adDate
End With
' Add it to the catalog
oCatalog.Tables.Append Item:=oTable
' It's good practice to set a primary key...
' Create the index, set its properties and add the ID
' column
Set oIndex = New ADOX.Index
With oIndex
.Name = "mytablePrimaryIndex"
.PrimaryKey = True
.Unique = True
.IndexNulls = adIndexNullsDisallow
.Columns.Append sKeyColumnName
End With
' add the index to the table
oTable.Indexes.Append oIndex
Set oIndex = Nothing
Set oTable = Nothing
' Now use ADO to insert some data
' Set up the invariant part of the command text
sCommandText = " INSERT INTO " & sTableName & _
" (" & sKeyColumnName & ", myText, myDate) VALUES "
Set oCommand = New ADODB.Command
With oCommand
.ActiveConnection = sConnectString
.CommandType = adCmdText
' set up and execute some commands to create new records
' Sometimes the first of these does not appear to work. I don't know
' why - ask an ADO expert
.CommandText = sCommandText & "(1,'mytext1','01/01/2004');"
.Execute
.CommandText = sCommandText & "(2,'mytext2','01/02/2004');"
.Execute
.CommandText = sCommandText & "(3,'mytext3','01/03/2004');"
.Execute
.CommandText = sCommandText & "(4,'mytext4','01/04/2004');"
.Execute
End With
' Clean up the ADO object
Set oCommand = Nothing
'GoTo q1
' now set up a command to create a query
' and add it to the catalog
Set oCommand = New ADODB.Command
oCommand.Name = sQueryName
oCommand.CommandText = " SELECT * FROM " & sTableName & _
" WHERE " & sKeyColumnName & " = 2" & _
" UNION" & _
" SELECT * FROM " & sTableName & _
" WHERE " & sKeyColumnName & " = 3"
On Error GoTo AddViewError
oCatalog.Procedures.Append Name:=sQueryName, Command:=oCommand
oCatalog.Procedures.Refresh
' Clean up the ADO object
Set oCommand = Nothing
' Clean up the ADOX objects
q1:
Set oCatalog = Nothing
Exit Sub
AddViewError:
' Difficult to know whether it's a "view" or a "procedure"
' Ask an ADOX expert!
If Err.Number = -2147217816 Then
' View or Procedure exists. Remove it and resume
bDeleted = False
For Each oView In oCatalog.Views
If oView.Name = sQueryName Then
oCatalog.Views.Delete sQueryName
bDeleted = True
Exit For
End If
Next
If Not bDeleted Then
For Each oProcedure In oCatalog.Procedures
If oProcedure.Name = sQueryName Then
oCatalog.Procedures.Delete sQueryName
bDeleted = True
Exit For
End If
Next
End If
If bDeleted Then Resume
End If
GeneralError:
' output some rudimentary error info.
Debug.Print Err.Number, Err.Description
End Sub
