Cmd to open AddTable Dialog (A2K/SQL Server)

[Jeremy Wallace]

Anyone know how we can open the add table dialog in an Access 2000 ADP
that's using a SQL Server back end?

We've got the toolbars and menus locked down and are using custom ones.
When they open a view in design view (via a query manager form), we run
code to show the correct menu and toolbar. The one thing we haven't
been able to do is open the "AddTable" dialog in Access 2000. In later
version we can use

DoCmd.RunComman d acCmdTableAddTa ble

But that wasn't introduced until Access XP, according to
http://www.tkwickenden.clara.net/list/listt.htm.

Here's what we're trying to do:
If SysCmd(acSysCmd AccessVer) = "9.0" Then
call DoCmd.RunComman d ([whatever this is])
'Or anything else that will get the job done
Else
call DoCmd.RunComman d (acCmdTableAddT able)
End If

In an mdb using a Jet back end (any version from 2K on), I can do
call DoCmd.RunComman d (acCmdShowTable )

but in the adp, with the connection to SQL Server, that doesn't
work--it's a different dialog box. The new command (acCmdTableAddT able)
works great in XP and 2003, but doesn't compile in 2000.

Any thoughts?

Jeremy
--
Jeremy Wallace
Fund for the City of New York

http://metrix.fcny.org

[Scott Berry]

Re: Cmd to open AddTable Dialog (A2K/SQL Server)

Jeremy,

See http://www.mvps.org/access/modules/mdl0016.htm

(I believe what you are looking for is acCmdNewObjectT able)

Regards
SB


"Jeremy Wallace" <jeremygetsmail @gmail.com> wrote in message
news:1141177299 .440924.107560@ u72g2000cwu.goo glegroups.com.. .[color=blue]
> Anyone know how we can open the add table dialog in an Access 2000 ADP
> that's using a SQL Server back end?
>
> We've got the toolbars and menus locked down and are using custom ones.
> When they open a view in design view (via a query manager form), we run
> code to show the correct menu and toolbar. The one thing we haven't
> been able to do is open the "AddTable" dialog in Access 2000. In later
> version we can use
>
> DoCmd.RunComman d acCmdTableAddTa ble
>
> But that wasn't introduced until Access XP, according to
> http://www.tkwickenden.clara.net/list/listt.htm.
>
> Here's what we're trying to do:
> If SysCmd(acSysCmd AccessVer) = "9.0" Then
> call DoCmd.RunComman d ([whatever this is])
> 'Or anything else that will get the job done
> Else
> call DoCmd.RunComman d (acCmdTableAddT able)
> End If
>
> In an mdb using a Jet back end (any version from 2K on), I can do
> call DoCmd.RunComman d (acCmdShowTable )
>
> but in the adp, with the connection to SQL Server, that doesn't
> work--it's a different dialog box. The new command (acCmdTableAddT able)
> works great in XP and 2003, but doesn't compile in 2000.
>
> Any thoughts?
>
> Jeremy
> --
> Jeremy Wallace
> Fund for the City of New York
> http://metrix.fcny.org
>[/color]

[Jeremy Wallace]

Re: Cmd to open AddTable Dialog (A2K/SQL Server)

Scott,

Thanks for the response.

Unfortunately, that's a link to a page that points to the page I've
gotten my information.

acCmdNewObjectT able creates a new table object. I'm looking to add an
existing table to a query.

Jeremy
--
Jeremy Wallace
Fund for the City of New York

http://metrix.fcny.org

[Jeremy Wallace]

Re: Cmd to open AddTable Dialog (A2K/SQL Server)

Hmm. One thought is to build a form that mimics the AddTable dialog,
and just modify what's in the SQL pane based on what the user does
there, not worrying about joins. People on the team are concerned about
the testing burdenhere, so this may well not make it into this release,
but for the future, and because I like this code, can y'all think of
states in which the query design window might be that would cause me
problems? What about sql statements that might cause this to fail?

Thanks much.

Jeremy
----

Function fnAddTableToQue ry()
On Error GoTo Error
Dim bolShowSql As Boolean
Dim bolShowDiagram As Boolean
Dim bolShowGrid As Boolean
Dim strSql As String
Dim intLoc As Integer
Dim cbrAdvancedQuer y As CommandBar
Dim strDelimiter As String
Dim strAddedTable As String

'In working code this would be a parameter of the function, but for the
test, I'll just hardcode it.
strAddedTable = "tblPayment s"

'turn off the diagram and grid panes and turn on the sql pane
Set cbrAdvancedQuer y = CommandBars("Me trixAdvancedQue ryToolbar")

'Record the state of the views
bolShowSql = cbrAdvancedQuer y.Controls("&SQ L View").State
bolShowDiagram = cbrAdvancedQuer y.Controls("&Di agram").State
bolShowGrid = cbrAdvancedQuer y.Controls("&Gr id").State

'turn off the diagram and grid panes and turn on the sql pane
Set cbrAdvancedQuer y = CommandBars("Me trixAdvancedQue ryToolbar")
If bolShowSql = 0 Then
Call DoCmd.RunComman d(acCmdViewShow PaneSQL)
End If
If bolShowDiagram = -1 Then
Call DoCmd.RunComman d(acCmdViewShow PaneDiagram)
End If
If bolShowGrid = -1 Then
Call DoCmd.RunComman d(acCmdViewShow PaneGrid)
End If

'Copy the sql of the query to the clipboard
Call DoCmd.RunComman d(acCmdSelectAl l)
Call DoCmd.RunComman d(acCmdCopy)
strSql = ClipBoard_GetTe xt 'This is a function in our library, I think
from the KB, though I'm not sure

'Modify the sql statement, adding the table
intLoc = InStr(1, strSql, " FROM ")
If intLoc = 0 Then
intLoc = InStr(1, strSql, Chr(10) & "FROM ")
End If
If Len(Trim(strSql )) - 5 > intLoc Then
strDelimiter = ","
End If
If intLoc < 19 Then
strSql = "SELECT " & strAddedTable & ".* FROM " & strAddedTable &
strDelimiter & " " & Mid(strSql, intLoc + 7)
Else
strSql = left(strSql, intLoc + 6) & strAddedTable & strDelimiter &
" " & Mid(strSql, intLoc + 7)
End If
Call ClipBoard_SetTe xt(strSql) 'This is a function in our library, I
think from the KB, though I'm not sure
Call DoCmd.RunComman d(acCmdPaste)

'Make sure the diagram is showing and turn of the sql pane
If cbrAdvancedQuer y.Controls("&Gr id").State = 0 Then
Call DoCmd.RunComman d(acCmdViewShow PaneGrid)
End If
Call DoCmd.RunComman d(acCmdViewShow PaneSQL)

'Return each of the panes to their original states
If Not bolShowSql = cbrAdvancedQuer y.Controls("&SQ L View").State Then
Call DoCmd.RunComman d(acCmdViewShow PaneSQL)
End If
If Not bolShowDiagram = cbrAdvancedQuer y.Controls("&Di agram").State
Then
Call DoCmd.RunComman d(acCmdViewShow PaneDiagram)
End If
If Not bolShowGrid = cbrAdvancedQuer y.Controls("&Gr id").State Then
Call DoCmd.RunComman d(acCmdViewShow PaneGrid)
End If

ExitPoint:
On Error Resume Next

Exit Function
Error:
Select Case Err.Number
Case Else
Call ErrorTrap(Err.N umber, Err.Description , "fnAddTableToQu ery")
End Select
GoTo ExitPoint
End Function

--
Jeremy Wallace
Fund for the City of New York

http://metrix.fcny.org