Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2003-11-25
Medium Priority
Last Modified: 2013-12-05
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
Question by:DougMc249
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 26

Expert Comment

by:Alan Warren
ID: 9822406
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



Author Comment

ID: 9917231
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."

Author Comment

ID: 9917241
Increased points.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 9932629
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.
LVL 26

Expert Comment

by:Alan Warren
ID: 9932670
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 :)
LVL 26

Expert Comment

by:Alan Warren
ID: 9932770
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 :)

Author Comment

ID: 9954027
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.
LVL 26

Accepted Solution

Alan Warren earned 1200 total points
ID: 9954125
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 :)


Author Comment

ID: 9961080
Thanks for the help.  With the information posted here plus switching to Office 2003, I could perform all of the table manipulation I needed.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question