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
7 3178
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 32,556
Expert Mod 16PB
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
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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. - Is it possible to link to the MS-SQL table [sysobjects]? If so, that is most of the difficulty resolved in one go.
- Alternatives would include setting up a PassThru query to the MS-SQL [sysobjects] table. That could also prove fruitful.
- 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.
- 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. Sign in to post your reply or Sign up for a free account.
Similar topics
by: Gurpreet Sachdeva |
last post by:
Hi guys,
Can anyone suggest some good tool for handling nested
tables in a HTML page... BeautifulSoup is somehow not working with
Nested Tables.
Thanks and Regards,
Garry
|
by: Mark P |
last post by:
I have a bit of vb code that uses Tables.Append to programatically
link tables from Oracle and DB2 datasources. The problem I am having
on some client machines is that the link will take anywhere...
|
by: Christoph Sticksel |
last post by:
Hi,
I'm having problems with attaching two tables stored in an SQL Server
2000 to an Access 97 database. It worked well for all other tables
except those two.
This is what I did: Choose the...
|
by: Matthew Wells |
last post by:
Good morning...
I have an Access front end that uses SQL Server linked tables. SQL Server
uses Windows authentication. I have one Windows group that all Access users
are a member of. I added...
|
by: Ulrike |
last post by:
I need to bind in (thumbnail-sized) pictures into an Access Database
in such a way that there is one picture for each record (abt. 3000
records on the whole). Ideally, the pictures should show on...
|
by: jomonto |
last post by:
Hello-
Here is my situation. I have multiple copies of the same database on
our MS SQL server (same program, but differnet funding streams). I have
an Access 2003 front end that I link to the...
|
by: trevordixon |
last post by:
Does linking tables with JOIN offer any significant performance
advantage or does it just make things more simple from a programming
standpoint?
Trevor
|
by: watto |
last post by:
I have a large database application with user data in a backend and
forms, code etc in a frontend. It includes a facility for migrating
data from an earlier backend to the current backend by...
|
by: bhobbs |
last post by:
We have a PostgreSQL database that contains several linking tables (all they contain is two foreign keys to link 2 other tables together). Is it necessary and/or advisable to specify the two...
|
by: xperre van wilrijk |
last post by:
Hi,
I have inherited an access userinterface that links to sql server
tables through ODBC. The SQL server database contains data related to
villages in my country and is populated by my...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |