SSMA Changes Table Names

[PiedPiper70]

Apologies if this has been covered before, but I can't find the answer, so here goes:-

I have a large A2010 multiuser application with a standard front end/back end split. I need to upsize the data to SQL Server, mainly due to database size.

I've run the MS Server Migration tool and it works fine (forgetting the errors for now!) but every table name has the prefix 'dbo' added. As a first step I am proposing to keep things simple and use ODBC to link the front end to the SQL back end, but how can I do that if the table names don't match?

Obviously I could go through and manually change every table name on the SQL server but that would be very painful. Is there a better solution please?

Thanks
Dave

 

[namliam]

Using this code:

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        ' ignore system and temporary tables
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            Debug.Print tdf.Name
        End If
    Next
    Set tdf = Nothing
    Set db = Nothing

you can list the table names, it should be quite easy to use this to change the tablenames to suite your needs.

 

[PiedPiper70]

Namliam - your code could be used to change the table names in Access but I don't see how that would help me. Unless I can stop the problem happening in the first place then I would need to change the table names on the SQL server back end, not on the Access front end.

 

[namliam]

The table NAME in the SQL server backend IS the same as it was in access....

DBO is the prefix for the "schema" or environment that your tables reside in.
inside that environment you can still do
Select * from OldTableName

Without any issues

 

[PiedPiper70]

What am I missing here?????

As an example - I have a table called tblMatters in my Access backend. My Access front end links to it and it is used by lots of queries etc in the front end and all works perfectly. Now, if I upsize using the SSMA then the table name is shown as dbo.tblMatters in SQLServer. Then if I try to link my Access front end to the SQLServer using ODBC then the link is made to dbo.tblMatters - after which nothing works.

So, it doesn't look like it to me, but if the table name is really the same in both places how can I link to it using ODBC. Or is there another way?

 

[namliam]

yes, look at it this way, if you link to your Access backend the link is to
C:\SomeFolder\YourBackend.accdb.tblMatters

You can make a pass through query to test it, connect the pass through to your sqlserver and have it do Select * from tblMatters, that should still return your full table without any problems.

"after which nothing works"
SQL server has its own thingies, have you set your PK etc in SQLServer

What exactly is nothing?

 

[PiedPiper70]

Just tried a pass through query and that worked fine. But I can't see how that helps me.

I have about 150 tables in my data and the front end has links to them all where the table name is tblMatters etc. If the table name in SQL Server was also tblMatters then I can link everything via ODBC and everything would work. And when I say work I mean all queries and vba code that referred to those table names, would all work. BUT the table names do not match. So either:-

1. I need to change the table names so they do match, then ODBC works
2. Find another way to connect all my front end tables to my back end data

 

[namliam]

the table name IS tblMatters

worse even you can create an odbc link to your tblMatters and call the (linked) table in your database anything you like, and it still be linked to tblMatters inside your sqlserver inveronment DBO. Exactly like you have it now linked to tblMatters inside your backend database. I probably sound like a broken record but yeah.

 

[PiedPiper70]

Well I think I've cracked it - thanks for persevering with me. This is what I do:-

1. Run the Server Migration Assistant for Access
2. Delete all the table links in my front end
3. Re-link to the SQL server tables using ODBC
4. Rename all the new links to remove the prefix "dbo_"

Just for your info, I have immediately encountered 2 new issues
1. During item 3 above the system tries to link to a huge number of tables that are not mine - I had to break out to stop that process
2. On trying to run my app if get multiple error 3622 wanting me to use dbSeeChanges in a huge number of places

Don't worry, these will become separate topics!!