Results 1 to 13 of 13
  1. #1
    RyanP is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    60

    Make Table Queries | Back End | Linked Table Manager

    Always using Split Databases.
    Never used Make Table queries too often, however started doing so.



    The Make Table queries by default store the table in the front end. Is there a way to force the table being created in the Back End instead / meanwhile automatically linking it to the FE (if required?)
    Just set the connection / database accordingly via VB?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Ive never tried, but i dont think so. Youd have to open control of the BE to build in it.
    Do you really need a make table that often? I NEVER had to use a make, users are confined with the same data over and over. They had no need to make new ones on their own.

    Cant you just create the new table in the BE, link it, and do append queries?
    Or let the users do make tables in the FE?

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe something like

    Code:
    
    Dim str As String
    str = "SELECT tblName.FirstField, tblName.SecondField INTO tblNewTable " & _
    "IN '\\ServerName\FolderName\Make Table via Connection\BackEndFile.accdb' FROM tblName;"
    CurrentDb.Execute str

  4. #4
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Front ends cannot (i believe) do that sort of edit to a back end that makes it pervasive to each user connected. They can do that on their own FE, but it will not be available to everyone.
    You can do the make table query in the _be and then you will have to re-link all the front ends to the _be again.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    RyanP,

    Perhaps you could describe the business rationale for the Make Table query. As others have said, this isn't a common requirement - especially if this is part of an operational system. if you are in development, then why not open the BE, create the appropriate table/fields, then carry on with FE development and testing?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by nick404 View Post
    Front ends cannot (i believe) do that sort of edit to a back end that makes it pervasive to each user connected...
    I have not tried it but I would guess it is possible and it might even be possible during Runtime.

  7. #7
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Quote Originally Posted by ItsMe View Post
    I have not tried it but I would guess it is possible and it might even be possible during Runtime.
    Seems like a somewhat dangerous ability to grant users, though.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by nick404 View Post
    Seems like a somewhat dangerous ability to grant users, though.
    Well, I don't think I would have an event triggered by a user directly create tables and start linking other FE's to the tables. But, there may be some reason to automate something like this. Usually, it has to do with development. Instead of spending hours building something, you automate it.

  9. #9
    RyanP is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    60
    The SQL is generated in VB in lieu of being a stored query (so that they can select the fields for export). Was using the Make Table query to store the recordset in a table unique to the logged on user, so it could then be exported to excel with an intuitive file name (the name of the made table TABLE_USERNAME_DATE.xlsx)
    It is likely that I am just being a newb and not using the right methods...

    End Goal: Run a dynamic SQL string and export the results to excel, meanwhile naming the workbook and worksheets accordingly.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe some DAO and CopyFromRecordset

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I have one database with process that allows users to customize an export. I use code that manages QueryDefs to modify query object. I have never published an executable database so don't know if this can be done in one. Here is an excerpt. Not showing code that builds the sql string.
    Code:
    Private Sub btnExcel_Click()
    Dim qdfUser As DAO.QueryDef
    CurrentDb.QueryDefs.Delete ("UserQuery")
    Set qdfUser = CurrentDb.CreateQueryDef("UserQuery", Me.tbxFilter)
    DoCmd.OpenQuery "UserQuery", , acReadOnly
    DoCmd.RunCommand acCmdExportExcel
    End Sub
    It is possible to have code in frontend that will build and link to tables in the backend. I have this and used it maybe twice. It was used more during development 9 years ago. I don't think this approach is suitable for your situation. Build table or query in frontend. Be aware that creating and deleting objects are design modifications and design modifications cause db to grow. Be sure to run Compact & Repair regularly.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    RyanP is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    60
    I used this method (query def) essentially and no longer using the RunSQL (Only reason I was using a make table query).

    1) Had to make a dummy query in the FE that gets overwritten.
    2) Build the SQL string & Default File Name as before
    3)

    CurrentDb.QueryDefs("dummyquery").sql = strSQL
    Dim fd As FileDialog
    Dim Title As String
    Set fd = Application.FileDialog(msoFileDialogSaveAs)
    With fd
    .AllowMultiSelect = False
    .Title = "Select a location to save the report"
    .InitialFileName = strFileName
    If .Show = True Then
    DoCmd.OutputTo acOutputQuery, "dummyquery", "ExcelWorkbook(*.xlsx)", strFileName & ".xlsx", True, , , acExportQualityScreen

    Else
    MsgBox "No file was selected"
    End If
    End With


    much smarter way of accomplishing this goal!
    thx

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I will have to revisit my code. I never did like having to delete and re-create query object but seem to remember could not get just changing the SQL statement to work. Thanks for followup with your solution.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linked Table Manager issue
    By siggybaby_18 in forum Access
    Replies: 11
    Last Post: 09-27-2013, 07:53 AM
  2. Linked table manager error
    By jim_jim in forum Access
    Replies: 5
    Last Post: 08-25-2010, 08:06 PM
  3. Linked Table Manager Problem
    By tallerthantrees in forum Access
    Replies: 4
    Last Post: 08-17-2009, 03:58 AM
  4. Using Linked Table Manager --Can't get it work
    By cccgsmith in forum Access
    Replies: 6
    Last Post: 03-11-2009, 06:43 AM
  5. Linked Table Manager
    By driccardi in forum Access
    Replies: 4
    Last Post: 02-15-2006, 11:47 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums