Amending Data On SQL7 Via Access Front End

Aug 17, 2000

Help!,

I've got an access front end containing various forms and sub forms, and we have just transferred the data into SQL, for storage, we can use the majority of the forms but, I now have a problem with updating the related data.

We have had problems updating the data, we need to close the form down in order to get amended data to register, just moving onto the next record give an ODBC error message.

Even using this method some details refuse to update, although the changes are initially visible on the form you cannot get them to transfer to the datafile.

the error message we get is

[microsoft][odbc sql server driver][sql server] the text, ntext, and image datatypes cannot be used in the where,having, or on clause, except with the like or is null predicates (#306)

we've checked the structures of the tables and the code in the form (it works in the old access back end).

anybody got any ideas

Please.

Cheers
Lee

View 1 Replies


ADVERTISEMENT

Filter SQL Server Data By User With An Micmicrosoft Access Front End

Sep 20, 2006

I am in the process of migrating 40 access databases to SQL Server and still use an access front end.All of these databases are Identical in structure and purpose. The goal is to centralize all of the databases backends into one Sql server database, but the data needs to
stay seperated by each location(user). I was able to add a location feild to all of the tables to keep the data seperate. Then I imported three of the locations data for testing. I would like to know how could I filter the data on the access frontend, so that it would ask the user for their location and only allow the user to access, update, and delete that Locations data ?

View 4 Replies View Related

MSSQL 2005 Inserting Data On Tables Created By Regular Users With Ddl_admin Role, Using Access Or Other Front End Apps

Feb 14, 2008

Writing to tables created by regular users on MSSQL2005

I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much

View 3 Replies View Related

Access Front End

Jan 31, 2005

i am almost finished building the front end for an application to manage restaurants. i am developing the front end with access vba, and intend to use sql server as a back end for the service. i just came across a group of people bashing access developers on a different site. i have several restaurants interested in using the service, and believe that the service should be very successfull (i have a very specific customer base that is currently not being targeted). the access component would not be a multiuser application. before i distribute the app i would appreciate any input. am i getting myself into trouble using access vba? should i run scared and switch to a vb.net web service?
thanks in advance for any thoughts.

View 1 Replies View Related

SQL 7 Security And Access 97 Front End

Jan 26, 2001

I am using Access 97 as a front end to access SQL 7 server on NT 4.0 server.
I've set up security model based on NT authentication only. Users have login right to login to SQL and they have public & dataread & denydatawrite access. They also have SELECT permission on a table object and have no permission to INSERT, DELETE and UPDATE.
When I use Access 97 to access a database, users are still capable of inserting and deleteing records in tables.

Am I doing something wrong?

Thanks, Michael.

View 1 Replies View Related

VB Vs Access As A Front End To SQL, Oracle, Etc

Jan 18, 1999

Maybe because I have worked mainly with VB as a front end to SQL Server so I am biased, but I now need definite reasons (I am on a committee for potential future directions) for using VB as opposed to Access for front ending SQL/Oracle, etc. I would also like to use ADO as oppose to DAO. Right now we are using Access with DAO.

Any ideas greatly appreciated.

View 2 Replies View Related

Access Front End Crawls

Oct 10, 2001

Hi,

We have just moved a largish Access database (180mb, 78 tables, largest
tables have about 250k records) to SQL 2k. The original app had an Access
back end (now loaded to SQL), and an Access front end (on each client) which is using some
local temporary tables, about 600 queries, and several thousand lines of
code using ADO and DAO). The Front end was relinked to SQL back end. When
testing everything seemed to run OK, but under load (15-20 users) the new
app just crawls. Routines that used to take seconds now take 10s of minutes.
ODBC timeouts or blocks are common.

Any idea why should the SQL back end be so much slower than Access. both the
Access back end and SQL2k are on the same server (Win 2k Adv. with RAID 5,
dual 600mhz Pent III, 512Mb RAM).
I realize that Access is not the best front end but that is what we have to work with.

Any help would be appreciated, as I am ready to swith back to Access.

Thanks,
Jakub

View 2 Replies View Related

Front End Options (access)

Oct 27, 2006

My company uses MS SQL Server for the back end and a Retail specific CRM as the front end. I wish to develop some internal peices of software for our use. I was planning on doing this with access.

my options are:

*Use access as front end and backend
*Use access as front end and SQL server as backend (create new DB)
*Use other front end and SQL Server as backend.

My question is, what are some good front ends that are availble for reletively small demands? How does Visual Studio come into play?

*Also, I would prefer to be able to create a .exe. I dont think access alows that. I would not want users to be able to go in (or even see) the tables and queries. They should only be able to see the one main menu form at the very least.

thankyou,
Dynasty

View 1 Replies View Related

Access Front-end - Concurrency

Oct 4, 2006

When I build an MS Access front-end for an SQL Server backend, how does it take care of data integrity and concurrency , if it is only a front-end ?
Is Access smart enough to do the job ?

Thanks.

View 1 Replies View Related

2 Sql Servers With Access Front-end

Jul 20, 2005

Hello,I have an sql server 2000 on the network and one installed locally inmy computer. I use access as front-end.I go through odbc to connect to the sql server on the network.I was wondering if it is possible to setup the access file to link toeither server.I need to do this because I would like to use the local server as testenvironment. I know I could achieve that by creating 2 users on my XPclient and having odbc liking depending of the users.Is any other way to do that without login and logoff everytime I wantto use a different database.thanks,Giovanni

View 1 Replies View Related

To Access The Connecton String From Front End

May 20, 2008

HI, i am working on ASp.net web applicaton , i am using the following connection string in my web.config file. <add name="X_Conn" connectionString="Data Source=XXX;Initial
Catalog=XXX;User ID=XX; Password=XXX;"
providerName="System.Data.SqlClient"/>
How can i get this connectionString attribute value from the front end. 

View 1 Replies View Related

Access Front-end/ SQL Backend -- Where Is All The Work Done?

Apr 18, 2001

If I'm using an Access front-end, and the data is on SQL Server being accesses via a linked table, and I create a query in Access, Where is all the work done?

I know access has the option of using a pass-through method, but if I do not use it, is Access processing the query locally? I plan on migrating several tables to SQL because the sizes are getting to large for Access and want to know if their will be a performance increase with out re-writing the queries in Access.

Thanks in advance,
Adam

View 1 Replies View Related

Access Front End To SQL Server PROBLEM

Apr 19, 2000

Does anyone know how to link (not import) an sql server table to an access
database using script? I tried a few methods but it doesn't seem to be working. Please help.

Thanks
Ziggy

View 1 Replies View Related

Access Front To Sql Server Backend

Mar 12, 2007

how can i do an access frontend to sql server backend

View 4 Replies View Related

Open Sessions Using MS Access As Front End

Mar 16, 2004

I'm using SQL server 7 on Win NT. I have Access 97 as a front end, with linked tables though ODBC to SQL Server. Everytime I open a table in Access, a session appears when I type sp_who2. I close that table in Access, but I when I type sp_who2 the table session is still present. Does anyone know a cause for this?

I am researching why sometimes when we close are queries and tables in Access we have sessions in SQL server that becomes orphans/ghost. I try to kill the session but can't, so therefore I have to recycle the database.

Any help would be appreciated.

View 2 Replies View Related

Access 2003 Projects As Front-end?

Aug 8, 2005

We use SQL Server 2000 on the back-end of our directory web site and ASP on the front end which works fine. However, for my own uses (since I don't create the asp and have to pay a programmer), would it be better to set up an Access 2003 project for my own data entry forms, standard reports and quick searching?

What would be the negatives of this approach. Remember, this is just for me.

Thanks in advance,
Kelly

View 6 Replies View Related

Migrating From Access To SQL Server With A Web Front End

Jul 20, 2005

Afternoon all,Apologies for cross-posting but as my query covers both Access and SQLServer I thought I'd send it both!I have inherited a project to migrate a fairly complex series ofAccess databases into a single proper SQL database with a web frontend.Its quite a nasty job as people are working on a variety of data setsat several Universities around the world and the data has got verymessy; hence the requirement to put it all on one live web enableddatabase server and provide a web-based front end (particularly assome users insist on using Macs so can't run Access as a front endanyway).If anyone could give me hints on how to perform such a migration or ifanyone knows of any good books or other documents on this I'd begrateful for assistance.Many thanksRich MayMuseum of London

View 9 Replies View Related

Front End Access And Back End As SQL Server

Sep 7, 2007



Dear Friends,
We have MS Access database with Forms and Reports, which was started 10 years ago by users and now the data is growing very rapidly.

Did anyone tried by having MS Access as front end and SQL Server 2000/2005 as backend with minimum modifications to the forms and reports in MS Access?

Please let me know, your ideas and if there are any links in the web or in Microsoft please provide here.

Thanks in advance,

View 1 Replies View Related

Using SQL Server CE As A Back-end To An MS Access Front-end

Mar 2, 2007

I have a back-end front-end application in MS Access. Instead of using MS Access queries it gets data via recordsets generated from SQL scripts in VBA routines.

I'm planning to upgrade it to use SQL Server as the back-end. However I need to retain the alternative option of using a file based back-end. (It currently has the capacity to be switched between alternative Access .mdb back-end files.) Is there any information available on how to do this and on how to get Access to synchronize between SQL Server and SQL Server CE back-ends? (Eventually the application will be migrated to VB.NET, but that is a long way down the track.)

View 1 Replies View Related

Cannot Update Tables Linked To Access Front-end

Sep 26, 2006

Hi,
I've a bit of a problem that I'm stuck on.

I have a system that has the tables on an SQL Server with the front-end on a Microsoft access database. They are connected via odbc and the tables are linked to the access front-end. The odbc connects to the sql server via a user created for the purpose that only has rights to this database. This user has rights set so that they can access/alter any data in the database.

When my users try to alter data on some of the linked tables they get "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time." This does not happen to every table only on some of them.

This happens even when there is only one user accessing the system. If I log into the enterprise manager I can alter those tables/ records without any problem (as some articles I’ve read say if the problem is connected to a corrupt record its not possible to then alter that record and sql server should give me an error message instead).

I have tired.
- Compact and repairing the access front-end.
- Deleting the links in the access front-end then re-linking them.
- Copying the objects from the access to a new blank access db.
- I have tried altering the account the odbc uses to login to the SA account.
- I have asked the server admin to do the 'compact and repair' on the s.q.l server, shrink I believe it is, as some articles suggest this could solve the problem.
- The admin has also tried this on the transaction files (so he has told me).

None of this has worked.

I'm stuck. I don't have any training on SQL server (nor will I be able to have any as I gather the training budget may be needed to fill some finance holes). If anyone has any idea of the answer to my problem or could point me in a direction that I could try investigating I would be very grateful. I have asked the admin if it’s anything to do with transactions, he has told me he does not know how to see those but he will try to find out if we can. Am I shooting in the dark or does this sound like a transaction/process locking problem? Is there something I should be looking for?

Thanks in advance.

View 3 Replies View Related

Parameterized Pass-through Queries From Access Front-end?

Jul 23, 2005

Is there any easy way to pass (dynamically) parameters to pass-throughqueries,when working with MS Access as front-end for SQL Server ?Thanks.

View 1 Replies View Related

Replace Access With A .NET Front End And SQL Backend Solution.

Jul 21, 2006

My company currently has about 20-25 Ms Access Database that they want toreplace the FE with .net and the BE on SQL.This will be done using Visual Studio 2005. Once the FE is converted to .netand the BE is SQL they all will be accessed through our intranet (sharepoint).I work in Ms Access and intermediate at VBA and just learing SQL through theENTERPRISE MANAGER SCREEN.I am just now looking at what Visual Studio 2005 is, but can some one tell mehow this will all connect?What is the typical route for this process?Ms access to SQL - upsizing wizard or SQL importing?????Ms Access FE to .net - summarize how this is done in visual studio (user facerebuilt) then placed on sharepoint?Can anyone sum this up?--Message posted via http://www.sqlmonster.com

View 4 Replies View Related

Passing Null Value From Front End Access To SQL Back End

Dec 19, 2007

Hi,

I'm not sure if this question should be in this thread but anyway...

I have a form (Mricosoft Access front end) in which the user enter a Pending Date in one of the fields. The date is populated into my backend (SQL Server) withouht problems.
Whenever the issue which had the pending date in the field is resolved I'd like to delete this date from this field and also from the back end. At the moment I'm not being able to pass the "value" Null to my back end (probably because it's a datetime type). I've searched the net throughout and I couldn't find a way around it. Only found solution to when the data is not date but numbers/letters, etc... Any ideas on how can I sort this out?

Many thanks
Emanuel

View 3 Replies View Related

Unable To Update Table Using Access 2002 Front End

Jul 20, 2005

I am unable to update a table (either by opening it and entering datadirectly or through a form). I have set the recordsettype property toupdateable snapshot and have set the permissions to update inside ofthe SQL Server 2000 to checked. Unfortunately, these items have notworked. Any advice is appreciated. Thanks.Dave Christman

View 1 Replies View Related

SQL Back - Access Front - Replication - Records 'jump'

May 12, 2007

Hi all :



I hope this is an easy one for some of you - this is the scenario :



1) I have an Access Application that has been upsized to SQL

2) We are now replicating it with a second office

3) When we started doing the Merge Replication, the ROWGUID was added to each table

4) When we attempt to add a new record to the table, it allows us to add 3 fields, then the record 'jumps' to the indexed location of the ROWGUID

5) The 'jump' is undesirable as we then have to resort the table by key and finish its input.

5) We have our own primary identity field that SQL seems to be ignoring.



Any help would be really great

View 4 Replies View Related

MS Access Front End To SQL Server Problem Passing Form Value To Report

Jul 23, 2005

I just changed my Access 2002 database to a SQL Server ADP project. Ihad a form where the user entered a value into a text box and when acommand button on the form was clicked a Report was opened. The reportsrecord source is a query. The query uses the value from the form textbox to restrict the query.Table name = EggsTableone of the columns in the table is named: EggColorForm name = EggColorFormForm text box name = ColorTextBoxThis sql worked for the query before I converted to SQL:SELECT EggsTable.EggColorFROM EggsTableWHERE (((EggsTable.EggColor)=[Forms]![EggColorForm]![ColorTextBox]));This no longer works. Can I change the syntax somehow to get this towork? I tried dropping the brackets around the word "Forms", I trieddropping all the square brackets, etc., nothing worked.I also tried just opening the report with the report's Server Filterproperty set to:EggColor=N'Forms.EggColorForm.ColorTextBox'I tried using the Report's open event to pass the form value directlyto the report. I tried setting a variable from the text box value onthe form. So far, nothing works. Any ideas?

View 7 Replies View Related

Front/backend Access Application-----conversion To MSSQL--advice

Dec 16, 2005

I have an application that uses Acces as a backend and VBA as front end. Application is secured and is supplied on a CD with setup.exe.

Can I use VB 2005 and MS SQL to achieve the same?

Would I be able to package my application with  all the neccessery files (assuming that client does not have any e.g. SQL server) so that multiple front ends can access data from common source?

Would I be able to secure such an application using only VS 2005?

What would I need to quickly learn  how to achieve the above ( any books you can suggest maybe)?

View 1 Replies View Related

Unable To Insert Records In A Mssql Database With A Access Front End

Nov 18, 2006

I have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.

View 3 Replies View Related

Converting Access 97 To SQL7

Jul 23, 2001

I am trying to convert the following Access 97 query into transact SQL for SQL Server 7 but can't seem to get the syntax correct.

"UPDATE datInventory INNER JOIN tmpInventoryReport ON (datInventory.DenomID = tmpInventoryReport.Denomination) AND (datInventory.ReportDate = tmpInventoryReport.ReportDate) AND (datInventory.RegionID = tmpInventoryReport.Region) AND (datInventory.ACCID = tmpInventoryReport.Carrier) AND (datInventory.OwnerID = tmpInventoryReport.[Financial Institution])
SET tmpInventoryReport.[Working Inventory] = [datInventory].[WIBalance], tmpInventoryReport.Surplus = [datInventory].[DSBalance];"

Can anyone help?

View 1 Replies View Related

Connection Problem From Access Front End Application To SQL Desktop Engine Backend

Jul 23, 2005

Hi there,I sincerely hope that someone out there can help. I have twoinstances of the SQL 2000 Desktop Engine running. One is on my localmachine for development and the other is on another machine on ournetwork which is the production environment. I have built an Access2003 front end application which connects to this database. Thisworks fine locally, as you would expect. I successfully installed thedatabase on the production machine and am able to connect to it viaAccess 2003 (using the Data Link Properties window) and from thirdparty database manager software (similar to Enterprise Manager). I amnot able to to connect to the database via my application.I am using the "sa" account with a strong password. This is myconnection string:strConnection = "Provider=sqloledb;DataSource=server02;UserId=sa;Password=strong;Initial Catalog=Test"The error I'm getting is:"Connection cannot be used to perform this operation. It is eitherclosed or invalid in this context."The connection string is the only thing that changes in my code when Iswitch from my local to my production database. Is there some reasonthat I can't use the "sa" account in this fashion that I'm not awareof? I'd rather not use integrated security for simplicity's sake asthis is a small, internal application. Also, I would have thoughtthat if that was the issue, I couldn't use "sa" at all, even locally.I'm going to post to the Access group as well but thought someone heremight have some advice to offer as well.Thanks,Barb

View 2 Replies View Related

Amending A Stored Procedure

Aug 20, 2007

Hi
I have a stored procedure that I have deployed with a database to an sqlserver.  To access the database and the stored procedure I am now using SQL Server Management Studio Express. When I was amending the stored procedure locally, I simply "opened" it, made the amendments and then "saved" it.  With S.M.S.E. if you click on "modify" a SQLQuery opens up and then after making the amendments you can save the SQLQuery.  How do you go from here to effect that change to the stored procedure resident on the sqlserver.
 
Chris
 
 
 

View 3 Replies View Related

Amending Jump To Url Properties?

Dec 7, 2007

Hi

I have a simple report which just lists product details and summary. So somthing like this:

Product Id | Hits | Sales

I want to add a action (Jump To URL) to the Product ID field, which then redirects it to its product page on a website, however, how do I pass the ID field within the url?

for example, typical product page on the website is

www.mysite.com/products/P1234.aspx

I want to do so that I get

www.mysite.com/products/P =Fields!ID.Value .aspx

but obviously that doesnt work!

Anyone have any ideas?

View 6 Replies View Related

Amending Query To Get Info From New Table

Oct 19, 2013

I used to use this code to extract coupon usage from two tables orders and coupons

select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber

[code]....

Following a database upgrade the Coupon Code is no longer used. Instead there are two new SQL tables called Promotions and PromotionsUsage.. The link between the two tables are

ID in Promotions

and

PromotionID in PromotionUsage

Both are the same value. So in order to see new orders for coupon usage how would I change this line in the original SQL to retireve this?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved