473,326 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Cmd to open AddTable Dialog (A2K/SQL Server)

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.RunCommand acCmdTableAddTable

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(acSysCmdAccessVer) = "9.0" Then
call DoCmd.RunCommand ([whatever this is])
'Or anything else that will get the job done
Else
call DoCmd.RunCommand (acCmdTableAddTable)
End If

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

but in the adp, with the connection to SQL Server, that doesn't
work--it's a different dialog box. The new command (acCmdTableAddTable)
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

Mar 1 '06 #1
3 3154
Jeremy,

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

(I believe what you are looking for is acCmdNewObjectTable)

Regards
SB
"Jeremy Wallace" <je************@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...
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.RunCommand acCmdTableAddTable

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(acSysCmdAccessVer) = "9.0" Then
call DoCmd.RunCommand ([whatever this is])
'Or anything else that will get the job done
Else
call DoCmd.RunCommand (acCmdTableAddTable)
End If

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

but in the adp, with the connection to SQL Server, that doesn't
work--it's a different dialog box. The new command (acCmdTableAddTable)
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

Mar 1 '06 #2
Scott,

Thanks for the response.

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

acCmdNewObjectTable 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

Mar 1 '06 #3
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 fnAddTableToQuery()
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 cbrAdvancedQuery 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 = "tblPayments"

'turn off the diagram and grid panes and turn on the sql pane
Set cbrAdvancedQuery = CommandBars("MetrixAdvancedQueryToolbar")

'Record the state of the views
bolShowSql = cbrAdvancedQuery.Controls("&SQL View").State
bolShowDiagram = cbrAdvancedQuery.Controls("&Diagram").State
bolShowGrid = cbrAdvancedQuery.Controls("&Grid").State

'turn off the diagram and grid panes and turn on the sql pane
Set cbrAdvancedQuery = CommandBars("MetrixAdvancedQueryToolbar")
If bolShowSql = 0 Then
Call DoCmd.RunCommand(acCmdViewShowPaneSQL)
End If
If bolShowDiagram = -1 Then
Call DoCmd.RunCommand(acCmdViewShowPaneDiagram)
End If
If bolShowGrid = -1 Then
Call DoCmd.RunCommand(acCmdViewShowPaneGrid)
End If

'Copy the sql of the query to the clipboard
Call DoCmd.RunCommand(acCmdSelectAll)
Call DoCmd.RunCommand(acCmdCopy)
strSql = ClipBoard_GetText '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_SetText(strSql) 'This is a function in our library, I
think from the KB, though I'm not sure
Call DoCmd.RunCommand(acCmdPaste)

'Make sure the diagram is showing and turn of the sql pane
If cbrAdvancedQuery.Controls("&Grid").State = 0 Then
Call DoCmd.RunCommand(acCmdViewShowPaneGrid)
End If
Call DoCmd.RunCommand(acCmdViewShowPaneSQL)

'Return each of the panes to their original states
If Not bolShowSql = cbrAdvancedQuery.Controls("&SQL View").State Then
Call DoCmd.RunCommand(acCmdViewShowPaneSQL)
End If
If Not bolShowDiagram = cbrAdvancedQuery.Controls("&Diagram").State
Then
Call DoCmd.RunCommand(acCmdViewShowPaneDiagram)
End If
If Not bolShowGrid = cbrAdvancedQuery.Controls("&Grid").State Then
Call DoCmd.RunCommand(acCmdViewShowPaneGrid)
End If

ExitPoint:
On Error Resume Next

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

--
Jeremy Wallace
Fund for the City of New York
http://metrix.fcny.org

Mar 2 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Alisha | last post by:
I'm trying to open the Common Dialog box to allow the user to navigate to a folder and select a file. The users are accessing the application on a single Citrix server, so the enviroment is the...
4
by: John | last post by:
Hi, I generate a report in a comma delimited file and give it a name like MyReport.csv . I then set a Hyperlink control to point tp the file HyperLink1.text = "Download"...
7
by: Mark Goldin | last post by:
If I want to open a new ASPX page when the user clicks on a button do I use JS for that? What would be a point of having AutoPostBack enabled for that button if I cannot use server side to open a...
0
by: AnandaSim | last post by:
Hi All, I've encountered times when VS 2003 won't connect using Front Page Server Extensions in some cases. Case: PC1 has VS. Creates a web project on machine IIS1. Seems to save an ..sln...
2
by: jefftim | last post by:
Hi All I've saved a word document in sql server database.I'm able to retrieve it from the database and open it in a word application. My problem is I'm able to open it in server/client machines...
0
by: Paul Brady | last post by:
I volunteer at a youth ministry agency and help them with their student database. They have two computers, both running Windows XP. Both have Office 2002 installed without Access, except that...
2
by: BillCo | last post by:
Does anyone know if it is possible to integrate SQL Server Reporting Services Reports in an ADP? Is there a plugin or anything that can do this? I have a hunch that the Web Viewer Active-X...
7
by: Peter | last post by:
ASP.NET 2.0 I am trying to open a Word document and Excel document from a dialog web page, what's the best way to do that? I have tried the following: Response.Clear();...
14
by: strat18 | last post by:
My database code works on my server and one laptop, but does not function correctly on two other laptops, all four systems run Vista Business and MSaccess 2007. The code is set up to display the file...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.