What is the best way to build tables using Access ADP which would easily join with SQL tables?

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
            objCatalog.Tables.Delete strTblName
        End If
    Next objTable

    ' 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, _
    idxSort.Columns.Append Item:="Version"

    objCatalog.Tables.Append Item:=objTable
    objTable.Indexes.Append Item:=idxSort

Debug.Print "table = "; objCatalog.Tables(strTblName).Name
AddedSQLServerTable = True
GoTo Exit_AddingSQLTable
    MsgBox Err.Description & Err.Number, vbOKOnly, "error", Err.HelpFile()
    ' Clean up the objects.
    Set objTable = Nothing
    Set objCatalog = Nothing
Exit Function
End Function
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alan WarrenApplications DeveloperCommented:
Hi  DougMc249,

An Access ADP cannot have local tables so any tables you are creating and dropping using ADOX belong to the sql database to which you are connected.

So when you executed  objCatalog.Tables.Delete strTblName if that table existed in the sql database, then it was dropped along with all the data. Take care here mate.

Apart from that, you seem to have it sorted out, maybe just add a line after creating the tables.


This should cause the tables to appear in the database window.

Heres a neat trick, Craete a table from an XML recordset it already contains all the types and properties.

Sub getXML()
  Dim sFldrPath As String
  Dim fso As New FileSystemObject
  Dim f As File
  Dim fls As Files
  Dim fol As Folder
  Dim fld As ADODB.Field
  Dim flds As ADODB.Fields
  Dim objCatalog As ADOX.Catalog
  Dim cn As ADODB.Connection
  Dim sUserXml As String
  Dim rsUser As New ADODB.Recordset
  Dim tbl As ADOX.Table
  Set objCatalog = New ADOX.Catalog
  Set cn = New ADODB.Connection
  Set fol = fso.GetFolder(sFldrPath)
  Set fls = fol.Files
  Set tbl = New ADOX.Table
  objCatalog.ActiveConnection = CurrentProject.Connection
  sFldrPath = "C:\Temp"
  cn.Open "Provider=MSPersist"
  For Each f In fls
    If fso.GetExtensionName(sFldrPath & f.Name) = "xml" Then
      Select Case f.Name
        Case Is = "User.xml"
          sUserXml = sFldrPath & "\" & f.Name
          Set rsUser = cn.Execute(sUserXml)
          tbl.Name = "User"  ' maybe f.name
          Set flds = rsUser.Fields
          For Each fld In flds
            tbl.Columns.Append fld.Name, fld.Type, fld.DefinedSize
          objCatalog.Tables.Append tbl
        Case Is = "Loan.xml"
        Case Is = "identification.xml"
        Case Is = "Customer.xml"
      End Select
    End If

' Set all your variable to nothing before exiting

End Sub


DougMc249Author Commented:
I have made some progress. I can now create the table I want by using a stored procedure called from a VBA sub but I still cannot do the same thing using ADOX.  When the code which appends the table is encountered, I get the error message "Err #3251 Object or provider is not capable of performing requested operation."
DougMc249Author Commented:
Increased points.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

DougMc249Author Commented:
Still trying to join the tables that I build in Microsoft Access ADP with the tables in the SQL database. I have built a table and I notice that it does not have the "(dbo)" after the table name where all of the SQL tables do have that after the table names.  When creating a new view, I can set the condition that the field fldEmpID in my new table is equal to the field EmployeeID in one of the SQL tables but I cannot make the view work.  Since I am using Office 2000, I am wondering if I perhaps need a different version of Office to work with SQL 2000 tables.  Maybe someone more familiar with this kind of work can tell me what software I need to join ADP tables with SQL tables.
Alan WarrenApplications DeveloperCommented:
Hi DougMc249

Like the idea of executing a stored proc to create tables, you can fix the dbo thing with built in proc in master sp_changeobjectowner.

Alan :)
Alan WarrenApplications DeveloperCommented:
Hi Doug,

Try this:

Public Sub doAdoxTable()
  Dim objCatalog As ADOX.Catalog
  Dim tbl As ADOX.Table
  Set objCatalog = New ADOX.Catalog
  Set tbl = New ADOX.Table
  objCatalog.ActiveConnection = CurrentProject.BaseConnectionString
  tbl.Name = "tblPeople2"
  tbl.Columns.Append "FName", adVarChar, 255
  tbl.Columns.Append "LName", adVarChar, 255
  objCatalog.Tables.Append tbl

  Dim rs As ADODB.Recordset
  Set rs = New ADODB.Recordset
  With rs
    .CursorLocation = adUseClient
  End With
  Dim sql As String
  sql = "Select * From tblPeople2"
  rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  With rs
    .Fields("FName") = "Alan"
    .Fields("LName") = "Warren"
    .Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Debug.Print .Fields("FName"), .Fields("LName")
  End With
End Sub

Alan :)
DougMc249Author Commented:
Referring to a newsletter at http://www.larkfarm.com/sax/sax11.txt , I can see 2 problems that I am having with Microsoft Access ADP are identified on this web page.  The first is described as follows :
 And speaking of SQL Server 2000, the news on that front isn't all good either. While SQL Server 2000 does offer major advances in many areas, it also breaks Access 2000 ADPs. For example, the New Database Wizard won't work to create a new database on a SQL Server 2000 database. Even if you do connect to an existing database, you'll find that the table designer doesn't work. This is frankly just stupid. Despite Microsoft's continual claims that Access is a strategic platform and that ADPs are part of the future direction of Access, the SQL Server 2000 and Office teams couldn't get their act together to fix these problems before SQL Server 2000 shipped. A fix is reportedly in the works, but there has not been any official announcement of a ship date. In the meantime, if you depend on Access to work with SQL Server databases via ADPs, don't upgrade to SQL Server 2000.
 and the second problem is described as follows :
  This issue I'd like to point out KB Article Q264950, "ACC2000: Error Message: ADO error: Line 1: Incorrect Syntax Near '\'. When You Try to Save a View." Yes, it's another ADP bug, and one which I hit myself, which is why this article is fresh in my mind. The basic issue is that there are circumstances under which saving a view back to SQL Server will give you an error. The culprit proves to be security: If you're not logged in as a member of the SQL Server db_owner group, you'll hit this bug. The article provides further details and a workaround besides the obvious one of adding yourself to that group, which isn't always possible. (You can get this, or any other, KnowledgeBase article by email. For the full text of this article, send an email message to mailto:mshelp@microsoft.com with the subject of Q264950. The article should be in your inbox within minutes.)

 I am hoping that the new office 2003 product will fix both of these problems.
Alan WarrenApplications DeveloperCommented:
Hi Doug,

I found the best way to build tables using Access ADP which would easily join with SQL tables is to build the tables locally in an MDB then use the upsizing wizard to upsize to sql2000 and ADP.

Seems to work pretty good, couple of bugs I noticed:
1... Autonumber fields are not defined as Identity fields in sql2000, need to rectify this manually.
2... Yes/No fields are not assigned a default value when converted to boolen, need to rectify this manually.

Apart from that it seems fairly painless.

Understand your frustration, personally I prefer to create my sql catalogs using Enterprise manager then create an ADP as the front-end.

RE: The article you posted: SQL SERVER 2000 BREAKS ADPs (October10, 2000)
This article was published over 3 years ago and may be considered a little out of date.

Good luck with your app, my friend.

Alan :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DougMc249Author Commented:
Thanks for the help.  With the information posted here plus switching to Office 2003, I could perform all of the table manipulation I needed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.