Solved

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

Posted on 2003-11-25
9
588 Views
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, _
       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
0
Comment
Question by:DougMc249
  • 5
  • 4
9 Comments
 
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.

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
 

0
 

Author Comment

by:DougMc249
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."
0
 

Author Comment

by:DougMc249
ID: 9917241
Increased points.
0
 

Author Comment

by:DougMc249
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.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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 :)
0
 
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
  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 :)
0
 

Author Comment

by:DougMc249
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 :
 ---------------------------------------------------------
  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.
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 300 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.
Regards

Alan :)




0
 

Author Comment

by:DougMc249
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.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now