What is the best way to build tables using Access ADP which would easily join with SQL tables?
Posted on 2003-11-25
What is the best way to build tables using Access ADP which would easily join with SQL tables? I am completely new to both SQL and Microsoft Access ADP. I have moved some VBA software from an MDB Microsoft Access file to an ADP file and am having difficulty building a new table using VBA and ADO. If the table already exists then I get the error message “Invalid procedure call or argument” when the “objCatalog.Tables.Delete strTblName” line is executed. If I manually delete the old table before executing this code then I get the same error, except the error occurs at the point of appending the table to the catalog. I am able to manually build the table without VBA and it appears with the designation “(dbo)” after the table name. When I build the table with a stored procedure then Access creates the table and it appears without the designation “(dbo)” after the table name but only appears after I close the ADP file and reopen it. Also, one thing that is confusing to me is that in the stored procedure which creates the table, I once made a mistake in the code and the error message referred to the table as follows : “table ‘company.dept\mylogin.mytable’; column does not allow nulls” and I have never seen tables referred to this way in Access. I am using Access 2002. I don’t have SQL installed on my PC but I am hooked to a server and really should not need it. This is probably really simple for someone who uses SQL and ADP files a lot but I don’t have much experience with these. Thanks in advance.
Function AddedSQLServerTable(strTblName As String) As Boolean
' This subroutine creates a table strTblName with two
' columns "File" and "Version". The Version column is indexed
' for sorting.
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO
Dim objCatalog As ADOX.Catalog
Dim objTable As ADOX.Table
Dim idxSort As New ADOX.Index
AddedSQLServerTable = False
On Error GoTo Err_AddingSQLTable
' Create the catalog and table objects.
Set objCatalog = New ADOX.Catalog
Set objTable = New ADOX.Table
' Connect the catalog to the current database.
objCatalog.ActiveConnection = CurrentProject.Connection
' If the tblSearchResults already exists, delete it.
For Each objTable In objCatalog.Tables
If objTable.Name = strTblName Then
' Now create a new tblSearchResults table.
Set objTable = New ADOX.Table
' Create the table, an index, and the File and Version columns.
objTable.Name = strTblName
idxSort.Name = "Sort_Version"
objTable.Columns.Append Item:="File", Type:=adVarWChar
objTable.Columns.Append Item:="Version", Type:=adVarWChar, _
Debug.Print "table = "; objCatalog.Tables(strTblName).Name
AddedSQLServerTable = True
MsgBox Err.Description & Err.Number, vbOKOnly, "error", Err.HelpFile()
' Clean up the objects.
Set objTable = Nothing
Set objCatalog = Nothing