Implementation Of A Backend/frontend Architecture Using SYNONYMS
Mar 28, 2008
Hi!
I am evaluating an architecture for one of our project... a SQL database containing the data (backend) and a second database containing the development code (frontend) linked to the backend with synonyms.
It enables to upgrade the code without touching the data. Or to change the backend / use a different set of data at will.
Everything was going fine, the behavior was expected to be EXACTLY the same with synonyms as with real tables. But I came accross a problem:
Let's say we have a synonym (frontend) dbo.TABLE1 that points to a table (backend) with a IDENTITY column.
I have a sp (frontend) with the following code:
INSERT INTO dbo.TABLE1...
SELECT @SCOPE_IDENTITY = SCOPE_IDENTITY()
Well in that case, @SCOPE_IDENTITY is NULL!
Anyone has ever faced that problem? Should I use another function to return the last ID inserted? Or is it the backend/frontend architecture that is completely flawed? I also heard there's a way, by creating the tables and the code on different filegroups, to restore only the tables or the code...
Thanks for your input!
Frantz
View 13 Replies
ADVERTISEMENT
Dec 2, 2006
I have a database with a sql backend and an access frontend. I put it in a folder on the network for users to access. First, they were getting errors like not being able to access the database if someone else had it open and not being able to perform certain functionality. It was suggested that i put a mde on each of the users pc. i gave them all access to their own front ends on the folder because they use different pcs. this solved the problem. however, i need a way to perform updates without manually creating mdes for every user. i used to work for a co that there was one mde was put into a folder on the network with a config file. The users access the database through CITRIX. Whenever they accessed the shortcut on the server, it created the users their own front end. Whenever we need to provide updates, we simply made a new mde from the mdb and whenever the user accessed the database again, they would have the latest mde and it would created an update frontend for the user. Does anyone know of such an utility or know what I am referring too? I encounted several suggestions like a batch file; but they refer to putting the file and the mde on each users pc. This is not how i'm set up. Thanks
View 10 Replies
View Related
Jun 15, 2001
Hello. I have an Access 97 frontend and a SQL 7 backend. When I try to open up the linked SQL tables in the frontend I recieve the error " This recordset is not updatable" from Access. I have the permissions in SQL set at this point to public having all permissions but it still does not change my problem. Does anyone know the answer?
View 3 Replies
View Related
Sep 26, 2005
Ok, little frustrated hence the excessive exclamation points.
I'm designing a database in access to use a sql backend. Table structures are setup and am creating a view to join multiple tables together for data entry purposes. Tables are joined properly but when I add more than one join, ie adding more than two tables, I can't add records!?!
I checked the joins, it's not the table, since if I add one table its ok, remove table, add another table and that works, but when I add both tables together, it prevents me from adding records!
And another thing, when I have just two tables joined together and add a record, it comes up with this save changes, drop changes or cancel thingie!!! WTF!!!?
Is there something simple i'm missing or is it my table structure????
Thanks in advance!
View 14 Replies
View Related
Jan 23, 2004
Hi, this is my first post (hopefully of many) on this board. Just wanted to say a quick hello before I dive into my question. :)
As the title suggested, I have to develop a MS Access form app (yes, it has to be Access - I know it sucks) that will post and query data to and from a remote SQL Server db. While I have no problem linking the two through the default ODBC drivers, my question is security. Some (actually most all) of the data being passed back and forth is sensative information, and I would like to know the best way to keep it safe.
If anybody has any suggestions, instructions, or can redirect me to a good FAQ site on the subject, I would be most appreciative. I have already done a search on these forums for an answer, but have sadly come up short.
Thanks to all in advance!
View 3 Replies
View Related
Jan 7, 2008
Hi,
We are new Access users and trying to take our database live...here are some of the problems we're facing.
When we split our Access database into a Backend and Frontends, a couple of strange things happened in the Frontend:
1.) When we run our queries, multiple copies of the same record are displayed (this is wrong).
2.) When we look at those queries in design view, the field names are preceded by Expr1, Expr2, Expr3, etc.
We have a large number of queries. How can we fix this problem?
Also, if anyone knows any websites or resources that can help us understand what things like Expr1 mean (a reference) that would be helpful too!
Thank You!!!
View 1 Replies
View Related
Jan 7, 2008
Hi,
We are not a very advanced group of access users and are trying to split our database for a research study...here is our question:
When we split our Access database into a Backend and Frontends, a couple of strange things happened in the Frontend:
1.) When we run our queries, multiple copies of the same record are displayed (this is wrong).
2.) When we look at those queries in design view, the field names are preceded by Expr1, Expr2, Expr3, etc.
We have a large number of queries. How can we fix this problem?
Also if anyone has a good source for trouble shooting this or simmilar difficulties we'd appriciate any spots or advice.
Thanks!
View 1 Replies
View Related
Jul 23, 2005
Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike
View 4 Replies
View Related
Aug 20, 2007
We are using an Access 2000 project to view our SQL Tables and using Access 2000 Runtime to Access the forms in the project. We have written in a locking system in VB and removed the video controls to prevent users from accessing the same records. But of course now we need to make the video controls available. This has now thrown up the problem of multiple users accessing the same records. We have tried to write code to lock records when then video controls are used but this is not working as well as we hoped. Can anyone please suggest any way of setting up locking on SQL using triggers from the Access frontend? or any other types of locking systems that could be written in the Access front end.
View 1 Replies
View Related
Apr 21, 2006
From what I'm seeing in MS documentation I would like to use SYNONYM to obtain information from a remote servers tables. I've tried to find some decient documentation on using it, but can't see to find a clear example for remote servers and in my testing I can make the SYNONYM but the conneciton is failing.
Any helpful hints or references would be welcomed.
Thanks,Ken..
View 8 Replies
View Related
Mar 28, 2007
Hi Experts:
I am writing a general API which would fetch synonyms from any database
providers (and would filter for a given schema).
I am using GetSchema method on DBConnection object and it works fine for
oracle.
SQL Server 2005 has a support for synonyms, but the above piece of code does
not work. Is there any API by which i can get the list?.
Thanks
AK
View 1 Replies
View Related
Nov 20, 2006
Hi all,
Just as a curiosity (and thoroughness) thing, why are there synonyms in SQL Server? When I say synonyms, i mean INT and INTEGER, OUT and OUTPUT: These word pairs mean the same thing!
And in some examples they chop and change between the use of these words... It adds to the confusion.
Anyhows I'm curious as to the "why" behind this.
Thanks
Chris
View 3 Replies
View Related
Nov 26, 2004
Hello ,
Where can I download synonyms database ?
Thank you !
View 5 Replies
View Related
Oct 28, 2015
I currently have a 2 node Availability Group that houses some big warehouse type of DB’s. It’s not uncommon to see DB1 populate DB2:
Use db1
Go
Insert into db2.dbo.table_1 values ‘ssfsfsfsf’
This all works as expected. However, we want to split these DB’s away from each other to reduce CPU impact on any one instance. Of course though we don’t want to have to go and change all the connection strings in our SSIS Packages. By default this would now require going to a 4 part naming convention like so: Insert into newServerName.db2.dbo.table_1 values ‘ssfsfsfsf’Again though, we are trying to avoid this. That said I was thinking to use synonyms. This works as expected, until a failover occurs, and the synonym is redirected to the local Read Only version of DB2.So in other words, SQL Server is behaving exactly as it should, but I need a way to work around it.
Example below:
USE [master]
GO
/****** Object: LinkedServer [DB2] Script Date: 10/28/2015 11:43:46 AM ******/
--note this is pointed to the AG Listener name, for failover purposes.
EXEC
master.dbo.sp_addlinkedserver
@server = N'DB2', @srvproduct=N'sqlserver', @provider=N'SQLNCLI',
@datasrc=N'myAGListenerName', @catalog=N'DB2'
[code]....
Again, SQL Server is behaving exactly as it should. However, this synonym is now fairly worthless, as it can't handle failing from one node to the other in the AG
View 2 Replies
View Related
Sep 18, 2007
Using the Latest JDBC Driver from SQLExpress I'm attempting to get use the getTable() method to get information about database objects that the user can access/alter. Works fine for for tables and views, but can't seem to get any information returned for synonyms.
Is it possible to get information for synonyms, e.g. column definitions? or am I'm missing some setting in the connection.
Any Help would be appreciated!
Dave.
View 5 Replies
View Related
Jul 9, 2004
I have a SQL Server database which has one user (UserA) which owns some tables. I've added an additional user (UserB) to the database such that it has access to the tables owned by UserA. What is happening is that when I log on as UserB I have to fully qualify table names and fields in my SQL statements when I deal with tables owned by UserA. Is there a way make the tables accessible without specifying the owner? In Oracle you could create a public synonym for the table eg. <table_name>. Wherever that synonym is referenced the DBMS would know thats its refering to UserA.<table_name>. Is such functionality available in SQL Server? Thanks.
View 2 Replies
View Related
Oct 10, 2006
we now used synonyms on the version 6 of our peoject, at first it turns out good. Then we migrate a database to the new database, everythings fine, its just a data migration anyway. The target database' structure and DML's wasn't change during migration. then intermittendly, we're encountering "A severe error occurred on the current command. The results, if any, should be discarded". I said its intermittent because sometime its ok, mostly its not functioning.
This is very painfull because all our scripts has this synonym. All our scripts uses an sp execution logging stored procedured of another database so we created a synonym for those sp's instead of fully qualifying the procedure name.
This is a major issue for us, please advise. You may response here, send me email or email me. Many Thanks.
Rodel E. Dagumampan
Email: dehranph@gmail.com
Yahoo: dehranph
Blog: http://community.devpinoy.org/blogs/dehranph
View 7 Replies
View Related
Sep 10, 2006
Hello
I'm looking for a way to store a large chunck of text (200 lines) in a variable which can be called in different objects within the database.
For example: I have several stored procedures that create the same temp table which exists of 200 column names over and over again.
It would have to look like this
Import myScript (I keep thinking of Import like used in .NET)
and myScript could exist of:
CREATE #MyTable(
...)
or even some simple text or a part of a sql statement.
I'm familiar with synonyms but you can't use that in this scenario.
Then I thought of functions. The scalar function returns a result, not what I want here. Table valued function return tables, not what I want.
Many thanks in advance!
View 3 Replies
View Related
Jul 17, 2007
When you create a stored procedure and give the user execute permission, you don't need to give the user select permission on the table used in the stored procedure.
If one of the tables in the stored procedure is a synonym referencing a table in another database, and the user is already in the other database, you get a select permission denied on that table and I could only get it to work if I gave the user select permission on that table.
Is there a way around that, since I hate giving select permissions on tables?
Thank you,
Wissam
View 3 Replies
View Related
Apr 14, 2008
I am using a sql 2005 database that contains a combination of local objects plus synonyms for objects from a second database. I have had no problems using synoyms for tables and views in reporting services but can't get synonyms for stored procedures to work as a report data source. All databases are on the same server and we have installed service pack 2.
Any suggestions?
View 2 Replies
View Related
Jan 3, 2008
Hi All,
Been doing some testing with SQLCMD and variables for installation scripts, enclosed below is some source code.
The one problem, I've been getting is the following error:
Incorrect syntax near 'certification'
This has been tested only in SSMS utilising the run in SQLCMD mode.
This error only occurs on the SQLCMD variable replacement, where it replaces $(Cert_Schema) for the word Certification.
Running the CREATE Synonym with the word Certification hard-coded into the script works.
Any ideas?
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Best Regards,
D
USE [Testing]
GO
/****** Object: Table [certification].[Table_1] Script Date: 01/03/2008 11:22:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [certification].[Table_1](
[PKId] [smallint] NOT NULL,
[Name] [nchar](10) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[PKId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--- SQLCMD Script below:
-- line below should read, colon setvar cert_schema 'certification'
etvar Cert_Schema 'certification'
-- The line below fails to work for some reason
CREATE SYNONYM crt_asa FOR $(Cert_Schema).Table_1
DROP SYNONYM crt_asa
-- Hard-coded equivilant line works
CREATE SYNONYM crt_asa FOR certification.Table_1
DROP SYNONYM crt_asa
View 3 Replies
View Related
Apr 17, 2003
The database adminsitrator decided that we use access as frontend for the SQL server which is very annoying because I have a hard time figuring out how to perform certain actions such as drop table queries and so. I have written my own little frontend in coldfusion but I rather work with access for security reasons.
My question:
does anyone have experience with using access as frontend and knows how to use it effectively, or knows where to get a manual? (or even better, a better frontend)
View 2 Replies
View Related
Mar 5, 2007
hi can you help me?
i need to make an access frontend to read specific queries from my sql server 2005 express backend.
how can i do this?
View 2 Replies
View Related
Mar 22, 2006
Hi,Right, i have a fairly good understanding of SQL. However, i have a fairly basic understanding of SQL Server.I know Storedprocedures are really good and i'm starting to use them. I understand they are good for making inserting, updating very easy.However when you look at a SQL Server database you get various folder, this leaves me a little confused with what they are all used for? whats the difference between these?Thanks in advance!sorry for the basic question, i'll try to challange you next time
View 1 Replies
View Related
Jun 22, 2004
Hi,
we are having performance problems with a pure MS Access database across a WAN.
As a test we want to setup a MSSQL server , move the data to the server and run the MS Access clients through a ODBC interface.
The application uses much SQL methods to get the data ( docmd.runsql commands ). Only a few native queries are used.
Will this help the performance ?
If not , what do i need to do to get a better performance ?
It is a lot of work to build a new application so if it is possible whith the existing code it would be very nice .
Thanks in advance.
View 1 Replies
View Related
Jul 20, 2005
Hello all!!!For a year and a half now, we have been developing different Access2000 applications. Most of them are access frontend and accessbackends. Some of them have SQL server 2000 as a backend. There is oneapplication, which consists of a frontend and 3 backend mdb's.Different companies (80) are using the application:1. Companies with just one pc and one user.2. Companies with a Cytrix environment.3. Companies with Normal network (pc's with WIN 2000, XP, 98, etc).Recently another company wanted to use the application and thiscompany has over 50 users and multiple locations. The first thing thatcame into my mind was: "let's make another version of the applicationwith SQL server 2000 as backend", BUT the thing is, we actually justwant one frontend which can communicate with (with normal performanceofcourse) both the access backend and the sql server backend. Thisway, we don't have to solve problems in 2 frontends, but just in one.My question is, is there anybody who has tried making an accessfrontend being able to talk to different kind of backends?Any help would be appreciated.Wilco
View 3 Replies
View Related
Apr 27, 2002
hi,
I am using Sql server 7.0.When i insert or update a record in a table,i need to
show a message in the front end ,that one record is added to the table.
I have written the queries for insertion and updation into the table in a storedprocdure.
Is there any way to return something from a storedprodure ,so that i can show a
message in the frontend, that a record is updated by someone..the updation will be
done in one machine(clerk's machine in bank) and the message should be shown in another machine(manger's machine in bank) who has more administrative power on the system.
does 'alert' in sql server help this.
Delphi is my frontend.
help
paul
View 2 Replies
View Related
Nov 9, 2005
Hi All,
I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?
Regards:
Prathmesh
View 3 Replies
View Related
Feb 1, 2008
My company is currently using access to manage equipment in 4-5 different locations. I want to move this to a sql database and have a front-end to do the same thing access is doing now.
Should I use access as a front-end or should I develop a custom front end using vb?
Just a fyi
5-7 tables
7-9 queries
10 or so reports and the front end is currently a switchboard that links to many other forms.
I'm looking for advice.
TIA
View 2 Replies
View Related
Feb 23, 2006
I am creating a Access frontend to some SQL Server Tables. One of these tables has an int type field and several money type fields. I am trying to populate the table based on text boxes on an Access form. Because of other reasons instead of just using the SQL table as the record source of the form I am using "INSERT INTO" statements to update the data.
Here is my problem if I leave the textfields that are supposed to populate the int and money fields blank I get an syntax error message on the INSERT statement. If I populate the textfields with a 0 the INSERT works fine.
The int field and money fields are defined to accept nulls so why is the blank textfield generating an error message instead of inserting a null into the table?
Any help or guidance would be greatly appreciated.
View 9 Replies
View Related
Jan 27, 2006
I have a website which runs off a Access database which I am currentlyconverting to sql server database. I would like to still use my accessfront end for reporting and queries..I created an odbc connection on my computer to the remote sql serverand created a link in access to my sql server tables. All my report andQueries run fine I just have one problem it keeps on prompting me forther password of the remote sqlserver database. I check the odbcconnection and I see the password is not saved in there even though Iput it in. Is there a way I can have the password saved. I do not wantkeep on having to enter the password.
View 4 Replies
View Related
May 9, 2007
I have a ms acssess db acting as a frontend to a Sql Server 2005 db. I have need to have the db setup as a standalone to distribute with the application for times when the 2005 server is unreachable. I have a created CE db that replicates the 2005 version.
What I want to do now is put the CE db on the desktop with the MS access frontend. Based on a user's preferences the connection would direct to the CE version.
The CE engine is loaded on the desktop, I have modified the ADODb connection string to point to the CE db. When I run it I get an error that says: "The provider can't be found. It may not be properly installed."
Here's the connection string:cnxn: ConnectionString = "Provider=microsoft.sqlserver.mobile.oledb.3.1;Data Source=spcd.sdf;User ID=;Password=;"
Am I trying to do something that just isn't going to happen?
Rick
View 5 Replies
View Related
Jun 29, 2015
I'm looking for a "God-script" to search for a given string (case invariant) in all DB objects (esp synonyms) in all DBs on a server?
View 9 Replies
View Related
