Results 1 to 7 of 7
  1. #1
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73

    Importing Tables from SQL Server

    These are two that I would love to solve.
    When developing the Access application I am working offsite and using a local copy of SQLServer to transact/test against. The copy is identical in structure to the actual SQLServer DB (accept of course the data in the tables is changing constantly).
    When I send a new version of the application to the customer they have to relink all the tables and in some cases we have found it better to delete the linked tables and re-import them. Which is what brings up the two questions that I hope all you really smart developers have an answer to:

    1) In previous versions of Access you used to be able to just choose your ODBC source and select all the tables you want to import and hit Relink and the Linked Table manager did the rest. Now in Access 2016 when you select the tables you want to import and hit relink one by one the you get a message box for each table that you have to respond to - time consuming at the least. So the question is - is there a way to stop this from happening such that when you select the tables to be imported then it just imports them without the message box over and over??

    2) This is more of a 'can you' question. After you do import the tables that you need they come in from SQLServer as dbo.table1, dbo.table2 etc. However in the application the forms/queries etc are looking for table1, table1 etc. Now I know that dbo is in front of the tables as it is part of the schema for the db in SQLServer and changing schemas is risky at best SO is there an easy way to bulk edit the table names kind of like a Find/Replace function that would allow me to say find dbo.table and replace the dbo. with nothing? Have searched Access help and Access to no avail.

    Thanks for any help!

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Investigate using DSN less connection strings
    You will need a reference table with the names (and optionally aliases) of all linked tables
    If you have more than one backend, you need a second table listing the connection strings for each BE
    It will solve both your problems
    a) relinking just involves looping through each table in turn - its very fast
    b) the table names & aliases do not require the use of dbo

    In addition it means there is no longer any need to setup ODBC links on each workstation

    This method may seem a bit more fiddly to set up the first time but after that MUCH easier.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    And to answer your 2nd question: Yes,you can. I found 2 routines to remove the "dbo_" prefix.

    1st one:
    Code:
    Public Sub subChangeLinkedTableNames()
    
        Dim dbCurr As DAO.Database
        Dim tdfCurr As DAO.TableDef
    
        Set dbCurr = CurrentDb()
    
        For Each tdfCurr In dbCurr.TableDefs
            If Len(tdfCurr.Connect) > 0 Then
                If Left(tdfCurr.Name, 4) = "dbo_" Then
                    tdfCurr.Name = Replace(tdfCurr.Name, "dbo_", "")
                End If
            End If
        Next
    
    
        Set tdfCurr = Nothing
        Set dbCurr = Nothing
    
    End Sub



    2nd one
    Code:
    '-------------------------------------------------------------------------------
    ' Procedure     :   TruncateDBO
    ' DateTime      :   3/13/2008 06:38
    ' Author        :   Donald R. Cossitt
    '               :
    ' Purpose       :   MS has the completely absurd notion that it is somehow 
    '               :   important to append 'DBO_' and or other database owner as a 
    '               :   prefix to linked tables. This routine truncates 'DBO_' from 
    '               :   the tables in the tabledefs collection having that 
    '               :   particular malady. However, will truncate any string 
    '               :   indicated by the user.
    '-------------------------------------------------------------------------------
    '
    Public Sub TruncateDBO()
    Dim tdfTable As DAO.TableDef
    Dim i As Integer
    Dim szDBOwner As String
    
        szDBOwner = InputBox("Enter prefix to truncate. [case sensitive]", _
                             "TRUNCATE DB OWNER", "dbo_")
        
        For Each tdfTable In CurrentDb.TableDefs
            If Left(tdfTable.Name, Len(szDBOwner)) = szDBOwner Then
                i = i + 1
                tdfTable.Name = Right(tdfTable.Name, _
                                Len(tdfTable.Name) - Len(szDBOwner))
            End If
        Next tdfTable
    
        MsgBox "Truncated : " & i & " table names", vbInformation, "'DBO_' GONE"
        
    End Sub

  4. #4
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    Thanks for the suggestion - I have long suspected that there is a better way to do this!
    Unfortunately I wouldn't know how to set up what you suggest - most of my programming in Access is done in the forms and queries so my overall abilities are at best the low end of middling!
    I know the tables don't need the dbo_ that is what they are named after importing them from SQL Server. Unfortunately the original db was developed using local Access tables (I don't think SQL Server even existed when I started this) and those tables were of course not named dbo_ so of course all references in the application don't have the prefix. As the application and amount of data grew I ported it over to SQL Server which is what names the tables dbo_something and hence the current situation. Appreciate the help!

  5. #5
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    Many thanks for the help!
    I take it that all I would need to do is copy the code and create a module which would then get called in the application by .....
    Can you just tell a module to run or would I open the module in VBE and run it from there???
    Sorry for the dumb questions - I have gotten into trouble recently with code and modules and am a bit gun shy at this point.
    Really appreciate all the help you get in this forum!

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by rcrobman View Post
    I take it that all I would need to do is copy the code and create a module which would then get called in the application by .....
    Can you just tell a module to run or would I open the module in VBE and run it from there???
    Yes, you can create a new module or use an existing nodule. Understand that a module name cannot be the same name as any other object.

    In the first code example in Post #3, copy and paste into a module.
    Click anywhere within the sub routine and press the F8 key to step through line by line. Press the F5 key to execute sub routine.


    The 2nd example will ask you to enter a prefix to remove..

  7. #7
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    Quote Originally Posted by ssanfu View Post
    Yes, you can create a new module or use an existing nodule. Understand that a module name cannot be the same name as any other object.

    In the first code example in Post #3, copy and paste into a module.
    Click anywhere within the sub routine and press the F8 key to step through line by line. Press the F5 key to execute sub routine.


    The 2nd example will ask you to enter a prefix to remove..
    Thanks so much for the help - sure glad that there are so many people willing to share their knowledge!

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

Similar Threads

  1. Replies: 2
    Last Post: 06-08-2017, 07:32 AM
  2. Migrating tables to SQL Server
    By Joakim N in forum SQL Server
    Replies: 15
    Last Post: 04-14-2017, 03:09 PM
  3. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  4. Replies: 4
    Last Post: 11-22-2013, 11:20 AM
  5. Importing Tables to access from server
    By masterjk in forum Import/Export Data
    Replies: 4
    Last Post: 12-05-2012, 10:58 AM

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