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.myta ble’; 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(strTbl Name 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.ActiveConnectio n = 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(strTblNa me).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
Function AddedSQLServerTable(strTbl
' 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.ActiveConnectio
' 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(strTblNa
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
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."
ASKER
Increased points.
ASKER
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 :)
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.ActiveConnectio n = CurrentProject.BaseConnect ionString
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 :)
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.ActiveConnectio
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,
With rs
.AddNew
.Fields("FName") = "Alan"
.Fields("LName") = "Warren"
.Update
.Close
.Open sql, CurrentProject.Connection,
.MoveFirst
Debug.Print .Fields("FName"), .Fields("LName")
.Close
End With
End Sub
Alan :)
ASKER
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.co m 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.
--------------------------
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.co
I am hoping that the new office 2003 product will fix both of these problems.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help. With the information posted here plus switching to Office 2003, I could perform all of the table manipulation I needed.
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.ActiveConnectio
sFldrPath = "C:\Temp"
cn.Open "Provider=MSPersist"
For Each f In fls
If fso.GetExtensionName(sFldr
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