Link to home
Start Free TrialLog in
Avatar of DougMc249
DougMc249

asked on

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, _
       DefinedSize:=50
    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
   
Err_AddingSQLTable:
    MsgBox Err.Description & Err.Number, vbOKOnly, "error", Err.HelpFile()
    Err.Clear
    Stop
   
Exit_AddingSQLTable:
    ' Clean up the objects.
    Set objTable = Nothing
    Set objCatalog = Nothing
   
       
Exit Function
End Function
Avatar of Alan Warren
Alan Warren
Flag of Philippines image

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.

objCatalog.Tables.Refresh

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
          Next
          objCatalog.Tables.Append tbl
          objCatalog.Tables.Refresh
        Case Is = "Loan.xml"
        Case Is = "identification.xml"
        Case Is = "Customer.xml"
      End Select
    End If
  Next
 

' Set all your variable to nothing before exiting


End Sub



Alan
 

Avatar of DougMc249
DougMc249

ASKER

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."
Increased points.
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.
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 :)
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
  objCatalog.Tables.Refresh

  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
    .AddNew
    .Fields("FName") = "Alan"
    .Fields("LName") = "Warren"
    .Update
    .Close
    .Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    .MoveFirst
    Debug.Print .Fields("FName"), .Fields("LName")
    .Close
  End With
 
End Sub


Alan :)
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 :
 ---------------------------------------------------------
  2) SQL SERVER 2000 BREAKS ADPs
  ---------------------------------------------------------
 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 :
 ---------------------------------------------------------
  7) KB ARTICLE: INCORRECT SYNTAX NEAR '\'
  ---------------------------------------------------------
  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.
ASKER CERTIFIED SOLUTION
Avatar of Alan Warren
Alan Warren
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the help.  With the information posted here plus switching to Office 2003, I could perform all of the table manipulation I needed.