Handling/Linking tables in access

[Dopey Fletcher]

Hello all,

Current Situation
Access (front end)
MSSQL (back end)
We have 2 environments, one test and one production
We develop in the test environment, and users work in the production environment..

All the tables in the Access are linked from the MSSQL database, and every time we bring the Access application to the other environment (so from test to production or vice versa) the tables are relinked. for this, we use the following code (this is pre-existing code written before i started working at the company)

tCount = CurrentDb.TableDefs.Count - 1
For i = 0 To tCount
        
        iPercent = i / tCount * 100
    
        TableName = CurrentDb.TableDefs(1).Name
        
        If Not TableName = "login" Then
            If Left(TableName, 4) <> "msys" Then
                'First delete the table from the list
                DoCmd.DeleteObject acTable, TableName
                'then add it again, in the correct environment
'PathEnv is the variable from which we get the Environment
                DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=SQLSERVER\SQLEXPRESS;Database=DB" & PathEnv & ";Trusted_Connection=Yes", acTable, "dbo." & TableName, TableName
            End If
        End If
    Next

Now the problem, when a new table is added in the test environment, and we copy the access application from the production to the test, it completely ignores this new table. This means that we have to add the table by hand. We work with several application all connected to the same database, and this is a time consuming job.

I want to know if it is possible to write some code that not only relinks all existing tables to the correct Environment, but also links the new tables found in the database.. I have tried working with the following code, but it seems that several tables are linked more then once, whilst others are not linked at all. I did comment out the second line in the previous piece of code to ensure that tables aren't linked twice

Set rs = ExecuteSqlQuery("SELECT * FROM sysobjects WHERE xtype='u' ORDER BY name ASC")
    Do Until rs.EOF
        DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=SQLSERVER\SQLEXPRESS;Database=DB" & PathEnv & ";Trusted_Connection=Yes", acTable, "dbo." & rs.Fields("name"), rs.Fields("name")
        rs.MoveNext
    Loop

Does anyone have experience with this and do you guys have any suggestions.. If anything isn't clear, let me know, and i'll try to explain it with more detail

[RuralGuy]

I would use this approach instead of what you have: http://allenbrowne.com/ser-13.html

[Dopey Fletcher]

Thank you RuralGuy for the link, but it seems that the back-end databases are Access files as well. What i need is some way to connect to the MSSQL server, get the correct database there (Tst or Pro), get all the tables from there and then connect from the access front end to all the tables.

[NeoPa]

First look at Link Tables in VB to see the basics of relinking linked tables. Although that thread deals mainly with Access linked tables I believe the concept is very much the same (though I don't have any SQL servers to test on I'm afraid).

With that understanding, I would make up some For Each code to process through your TableDefs() collection (but always use a variable to store the value of CurrentDb() as it's a function that returns a database object and each time you use it a new instance is created). For each TableDef check the .Connect property to see if it's one you wish to process.

ODBC;
Driver={SQL Server};
Server=SQLSERVER\SQLEXPRESS;
Database=DB" & PathEnv & ";
Trusted_Connection=Yes

If the PathEnv part of it is simply a choice between "Tst" and "Pro" then you can use code as simple as :

With {tabledef object}
    .Connect = Replace(.Connect, "Tst", "Pro")
    Call .RefreshLink
End With

[Dopey Fletcher]

Thank you very much for the suggestions you have just given me, I will take this into consideration and see if this would help.. Allthough i must say that we don't have any problem with the code currently used.

On another point, the suggestion you have provided does not include the fact that there are other tables in the MSSQL database which aren't included in the access file yet. I need some way to find these tables and add them into access as well

[NeoPa]

I was never really clear what you were asking for with that second part. Do you mean you want each table that exists in the MSSQL database to be linked to from the Access database regardless of whether or not it has already been linked? If so, that's something I've never even considered before (It's an unusual request to say the least). I suppose you want the FE Access database to be like a view into the BE SQL database regardless of which tables had ever previously been used for anything.

Assuming the information is available somewhere as to what tables exist, I expect it could be managed, but I don't even know where you'd find such information I'm afraid.

If you do manage to find this then I would suggest two separate procedures :- One for relinking existing tables and another, separate, one for finding and adding links to unlinked tables from your BE SQL database which have been added recently. It's always an option to call both procedures at the same time if required, but I expect there'll be occasions when each is required separately.

[Dopey Fletcher]

I'll try to explain it with a small example:

I currently have 15 tables in the Pro database, and the same amount of tables in the Tst database. Now i add a new table called LogReports to the Tst database. Offcourse when the forms around this new table are created and tested, it will be imported in the pro database as well

When i get the version from the Pro environment (which is linked to the Pro database) and place it in the Tst Environment, the application knows to relink all current tables to the Tst Database. This is part 1 of the problem, and you and RuralGuy have given several options to improve this. But when i start the FE access application, it does not automatically import the new LogReports table.

I know there is a way to use a query to get all tables in an SQL database. this query is
(–list of all user defined tables
select * from sysobjects where xtype=’u’)
(from http://blog.sqlauthority.com/2007/06...s-of-database/)

But now i need to find a way to get all the new tables in the database, and link to them from the FE access application. Probably most of the time there won't be any new tables, so this won't be needed then.

So i was thinking about something like this:

If sql.tablecount <> access.tablecount then
   Foreach table in sql.tables
      check if table already exists
      if so, ignore
      if not, link to the table from access

but now i don't understand how i can create this code within access. it seems that the access tablecount always differs from the sql tablecount, and allthough there are several tables missing from the list, there are more tables then in the sql database

If there is anything not clear, let me know and i'll try to explain it

[NeoPa]

First of all there is a fundamental problem transferring data from an MS-SQL data database to an Access FE one. Hence the need to create linked tables. Having somewhere in the MS-SQL database that provides a list of available tables to a T-SQL query doesn't, in itself, allow that data to be available to your FE database. Not to say that it's impossible. I'm sure it's not. Nevertheless, I don't have an MS-SQL server to play with so you will need to provide an answer that makes sense from within Access. I may be able to make suggestions that you can consider/try out, but that's as far as I can go at this stage.

1. Is it possible to link to the MS-SQL table [sysobjects]? If so, that is most of the difficulty resolved in one go.
2. Alternatives would include setting up a PassThru query to the MS-SQL [sysobjects] table. That could also prove fruitful.
3. Using the table count to determine if the databases are already matched is inherently flawed. There are often more tables than those you see, so it makes no real sense to approach it that way.
4. When you have access to the table list, I would suggest an approach as follows :
   For each table in the list check each linked table in your FE database and if there is a match then ignore it.
   If there is no match then create a new link to the table in your FE database.