Delete Query In Access W/SQL Back-End
Nov 15, 2007
I Have read a few of the postings and I hate to beat a dead horse. But I am desperatly looking for your Help...
I am converting all of my Access Databases over to SQL server 2005. but the catch is that I need to keep Access as my front-end. One of the problems that I have faced (and there maybe more) but when it comes to running queries (especially Delete), I cannot seem to get things to work.
I would like to just store all of my data in SS2K5 and have all of my queries and forms run in Access.
Why is it that I am getting errors that do not seem to make sense (Access Error 3086) when I have opened up all of the permissions associated to my database?
Please let me know if anyone has any type of solution, or some place that I can go to that will help me understand this a little more.
my query is pretty simple.
DELETE Opsec_View.*
FROM Opsec_View;
View 1 Replies
ADVERTISEMENT
Oct 6, 2007
I have a SQL CE database that is synching up with a SQL Express database (therefore no replication).
I do a pull down (with RDA) Table XYZ with no rows.
I fill out the rows on the PDA
I push the changes back to SQL Express
What I would like to do now is selectively delete some rows from the CE Database. BUT, I don't want those rows to delete from the SQL Express master database.
How can I do this?
View 8 Replies
View Related
Feb 15, 2008
Hello all ASP'ers, I'm very familiar with .net code and data access as it is what I do for a living. However, what I don't do a lot is make my own web project from scratch. All our DB connections are handled through a DB class that we instansiate and use, quite easy. BTW, we hit an Oracle DB and code in C# in VS 2008 Team Developer.Here's my (simple I hope) dilemma.I'm using VS2008, C#, SQL server database. I put a text box and a button on the front page (of my web page) for users to enter their email address for a newsletter. I created the DB by right-mouse-clicking on my project icon in the Solution Explorer, add new item, then selected SQLServer Database, it and saved it. It now appears in the Solution Explorer. I've added the table and columns I need but for the life of me I cannot connect to it. I've seen TONS of connection string examples but I need help getting the proper syntax for my situationI have not created a user ID or a password for the database. What do I put in for the DataSource and the database?I dragged the database icon into my code and got the local path wher it resides:C:FilesWebProjects2008WbProjApp_DataNews.mdfHere is the most common connection string I see for SQL Server:SqlConnection Conn = new SqlConnection(" + "Data Source=Your server name;" + <------ What goes here?"database=Your Database Name;" + <------ What goes here?"UID=UserID of the server;" + <------ Can I get rid of this?"PWD=password of the server;"); <------ Can I get rid of this?I've done a different project using an Access DB and have that guy up on the web working great, I just can't get htis one connected and the data a' flowin'.Please help, this is my personal website and I want to use SQL Server but I'm getting frustrated and just might go back to Access, aaaaggghhhhhhh...Once I get past the Conn.Open(); in a try/catch I'll be good. I've got the rest of the data commands in place and ready to go. Here is a link to a visual to help you see what I'm doing, it's just a shot of my solution explorer.
View 3 Replies
View Related
Apr 6, 2004
I am trying to set up an Access db to connect to a SQL server back end. I created a system DSN and then linked the Access db to it; no problem when the Access db is local.
When I upload the Access db to the website, it refuses to connect; I keep getting this error message:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'SQS2008' failed.
Here is the ASP code in the web page (pretty plain vanilla):
DIM Connect2
SET Connect2=Server.CreateObject("ADODB.CONNECTION")
DSNTemp="Driver={Microsoft Access Driver (*.mdb)}; "
DSNTemp = DSNTemp & "DBQ=" & server.mappath("yetanothertest.mdb")
connect2.Open DSNTemp
SET onwash2=Connect2.Execute("SELECT * FROM LineStandardPolicies")
Any help would be appreciated. Thank you!
View 2 Replies
View Related
Sep 12, 2004
Hi,
I'm a MS Access developer who needs to help someone migrate/convert from a 'finished' SQL application to Access. How do I gain access to the SQL backend so I can examine/export the raw data? For example, in Access, I would hold down the Shift key while opening the program and it would give me editing rights to the database.
Please be very specific because I have zero experience with SQL.
Thanks,
David
View 7 Replies
View Related
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
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
Aug 3, 2015
I have user called DBA_USER he has db_owner access on (123DB) database ....
But when ever my machine restarted the user was getting access error (i.e.. you don't have access on this database) and i checked his access on that particular database his access got reverted back ....
Here my question is why this particular user getting this type of error ....
View 7 Replies
View Related
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
Mar 15, 2007
I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
Any advice would be greatly appreciated.
Many thanks
Smilla
View 1 Replies
View Related
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
Nov 11, 2015
I have removed all constraints of a table.I have a copy of the database as back up, now how can i add back the constraints to the removed table.
View 6 Replies
View Related
Nov 26, 2007
this is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you
View 1 Replies
View Related
Feb 16, 2008
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
View 2 Replies
View Related
Sep 3, 2015
I cannot open now my database after changing it name from the folder where it is save and also changed back to its original name.Message recovery pending now is appearing as shown below.Any chance that I could recover my database back?
View 17 Replies
View Related
Jun 15, 2006
I have a proc to get the rowcounts for the given date range.
I have to get the row counts for each day going back to 6 months on the table.
With this proc i can get one day's row couts.. i need to loop through for all dates.
Please can someone get me the code for this.
create proc p_rowcounts
@Date1 datetime,
@Date2 datetime
SELECT
count (*) as 'Number of Rows', @Date1 as Date
FROM
Table1 (nolock)
WHERE ModifyTime >= @Date1 and ModifyTime < @Date2
thanks for the help.
View 3 Replies
View Related
May 1, 2015
I'm written a query to pull adjustments made on customer accounts, and I am attempting to pull in the payment associated with the adjustments. I'm using a single account to test my query and somewhere in my final output select statement I'm doing something that is bringing me back more records than I want. The code I have written is:
IF OBJECT_ID('TEMPDB..#TMP1O') IS NOT NULL DROP TABLE #TMP1O
select a.batch
,a.acctcorp
,a.house
,a.cust
--,RIGHT (REPLICATE ('0',5) + CONVERT(VARCHAR(5),A.acctcorp),5) +
[Code] ....
I've tried making changes within my where statement and parameters, but so far I've come up emtpy.
View 9 Replies
View Related
Apr 24, 2007
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies
View Related
Aug 17, 2006
Hi,
I have a question to ask. I'm new to SQL Server but have been trying to learn it. I have just installed the SQL Server 2000 SP4 and have a dummy Access database that need to be migrated to SQL Server 2000 for a test. I used the DTS wizard provided by the SQL Server 2000. It was successful - no error appears. I have also successfully done the link tables from Access to SQL Server. Now, my question is how do i add/edit/delete some records in SQL Server via Access. I need your help or guidance on this.
Thank you in advance
View 2 Replies
View Related
Jul 23, 2005
I cannot execute my sql to create a table with ON DELETE CASCADE option.Here is my sql:CREATE TABLE Employees (Name Text(10) not null, Age number,CONSTRAINT pkEmployeesPRIMARY KEY (Name)); <--- This is ok!CREATE TABLE CanTake (Name Text(10) not null, Make Text(10) not null,CONSTRAINT pkCanTakePRIMARY KEY (Name, Make),CONSTRAINT fkCanTakeFOREIGN KEY (Name) REFERENCES EmployeesON UPDATE CASCADE <--- MS-ACCESS says 'syntax error'ON DELETE CASCADE);When I didn't include ON UPDATE CASCADE ON DELETE CASCADE in sql, it works(but that's not what I want). Why? Could anyone suggest me?Thank you.--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related
Sep 28, 2006
HI
I want to delete all tables from an MS Access database.
i cannot use the designer . i have to do it thru an sql statement
a bunch of statements will also do . .
any body has a solution ??
P.s: All replies will be appreacited
View 1 Replies
View Related
May 14, 2015
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
View 2 Replies
View Related
Jun 9, 2015
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
View 4 Replies
View Related
Jun 16, 2007
Hi ,
i am working with .net and as backhand using mySql.
i have done all configration to use the database drive, my problem is i want to insert a path of my xxx selected file from open dialog box. we all know very well that paht has back salesh like "c:data estfilename" that is cause of my problem when ever i insert a path into my table of mysql it accesses all put in different way like "catatestfilename".
that is what i hope u understood what i want to know.
any one who has some idea please mail me.
i will thankfull for your's concern.
bye
keep thinking.
View 3 Replies
View Related
Jul 26, 2007
i am using asp.net vb, i have 2 table show as below if i want to delete the forumid 1 row,then how would i delete the topic table who belong to the forumid 1.
how would 1 do it if i am using gridview
Forum table
Forumid | Forumname
1 | hi
2 | me
Topic table
Topicid | Forumid | Topicname
1 | 1 | yo
2 | 1 | everyone
3 | 1 | google
View 1 Replies
View Related
Oct 28, 2015
I have a person table
CREATE TABLE Person
(
PersonID INT
Name varchar(50),
HireDate datetime,
HireOrder int,
AltOrder int
)
Assume I have data like this
INSERT INTO Person VALUES(1, 'Rob', '06/02/1988', 0, 0)
INSERT INTO Person VALUES(2, 'Tom', '05/07/2016', 0, 0)
INSERT INTO Person VALUES(3, 'Phil', '01/04/2011', 1, 0)
INSERT INTO Person VALUES(4, 'Cris', '01/04/2011', 2, 0)
INSERT INTO Person VALUES(5, 'Jen', '01/04/2011', 3, 0)
INSERT INTO Person VALUES(6, 'Bill', '01/05/2011', 0, 0)
INSERT INTO Person VALUES(7, 'Ray', '01/23/2012', 0, 0)
I'm trying to simplify my requirement... providing the input of HireDate, HireOrder, and AltOrder, I need to be able to pick up the next person
For ex:, if I provide input, HireDate: 06/02/1988, HireOrder:0, AltOrder:0, the return value expected is "Tom" because he is the next person after the provided input.
For ex:, if I provide input, HireDate: 05/07/2016, HireOrder:0, AltOrder:0, the return value expected is "Phil" because he is the next person after the provided input. Though Phil and Cris have same dates, their HireOrder takes precedence in this case. If they also have same HireOrder, AltOrder would be coming in picture to determine next person
Another ex: if I provide input, HireDate: 01/04/2011, HireOrder:1, AltOrder:0, the return value expected is "Cris" because she is the next person after the provided input. Here hireorder determines.
If I provide, HireDate: 01/23/2012, HireOrder:0, AltOrder:0, as there is no person after this, I should be able to pick the first person on the list - in this case Rob.
I can write some business logic in front-end, but I thought it would be good, if I can move this to a stored procedure which can return me the PersonID for optimal performance.
I have tried writing various conditions but couldn't achieve a query that meets all my requirements here.
I'm even fine if my last condition is not met (returning the first person in the list, in case no one is available after the provided input).
View 4 Replies
View Related
Aug 18, 2014
Currently I have a standard query with a join to several tables. There are two additional tables MAS_CTB and MAS_STB. I would like to do a union between those two tables to get FIELDVALUE which will exist in either the CTB table or the STB table and then have that value be returned with the results of the original query.
I can of course write a UNION from the main query to the CTB and then to the STB table, however it's about 80 dummy fields I would have to replicate in the union which is why I was wondering if there was a more simple way.
Main Query:
SELECT Field1, Field2...Field80
From Table1
Join Table2
Union Portion:
Select FieldValue <------
From MAS_CTB
UNION
Select FieldValue <-----Return to main query above
From MAS_STB
View 3 Replies
View Related
Jul 20, 2005
Hello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
View 2 Replies
View Related
Jun 29, 2004
I like to give First Name and Last Name in two different text box and then hit the delete button (command button). Then it will do a query to find the person and delete the corresponding record from the table. Any kind of help will be appreciated. Thank you.
View 1 Replies
View Related
Jul 17, 2015
We are using MS SQL Server 2008. I am running a batch job which deletes 21 days older records(6-7 million records). But daily we have transaction is going on in the database. When the delete occurs, all the insert statements got blocked and waits till the delete statement to complete. May I know why the blocking occurs?
View 3 Replies
View Related
Sep 4, 2015
I have to truncate access table before I insert records to access database table.
I tried using Delete From Table_name or Truncate table Table_NAME and I have used Microsoft Jet 4.0 OLE DB Provider. It does not seem to work. I read some post on forums. None of them seem to work while truncating or deleting records from one of the access database table.
Is there any easy way to truncate access database without using script component and VB scripts. I do not know how to Write VB scripts so trying to find alternatives.
[URL]
View 5 Replies
View Related
Sep 4, 2015
I have a ssis package which identifies duplicate records in access database. I have staged access database into sql sever and created ssis package. Now, I have final list of records which needs to be delete from access database and new records which are to be inserted into access database.
What do I need to do if I want to delete those duplicate records directly from access database using SSIS. I cannot truncate whole access database and reload. I just have to delete duplicate rows from access db and add new records.
View 9 Replies
View Related
Apr 18, 2007
Hi,
I have an application that uses following code:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
namespace TimeTracking.DB
{
public class sql
{
OleDbConnection conn;
//
//the constructor for this class, set the connectionstring
//
public sql()
{
DBConnectionstring ConnectToDB = new DBConnectionstring();
conn = ConnectToDB.MyConnection();
}
//
//
//
public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment)
{
int m_ID = ID;
int m_Week = (Convert.ToInt32(Week));
int m_Year = (Convert.ToInt32(Year));
string m_Date = Date;
string m_Project = Project;
int m_ProjectID = new int();
string m_Action = Action;
int m_ActionID = new int();
Single m_Time = (Convert.ToSingle(Time));
string m_Comment = Comment;
//
//get the project ID from the database and store it in m_ProjectID
//
OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject"
+ " WHERE (((tblProject.Project) LIKE @Project))", conn);
SelectProjectID.Parameters.AddWithValue("@Project", m_Project);
try
{
//open the connection
conn.Open();
OleDbDataReader Dataset = SelectProjectID.ExecuteReader();
while (Dataset.Read())
{
m_ProjectID = (int)Dataset["ProjectID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//get the action ID from the database and store it in m_ActionID
//
OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction"
+ " WHERE (((tblAction.Action) LIKE @Action))", conn);
SelectActionID.Parameters.AddWithValue("@Action", m_Action);
try
{
OleDbDataReader Dataset = SelectActionID.ExecuteReader();
while (Dataset.Read())
{
m_ActionID = (int)Dataset["ActionID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//
//
OleDbCommand Query = new OleDbCommand("UPDATE [tblEntry] SET [tblEntry].[Weeknumber] = @Week,"
+ " [tblEntry].[Year] = @Year, [tblEntry].[Date] = @Date, [tblEntry].[Project] = @ProjectID, [tblEntry].[Action] = @ActionID,"
+ " [tblEntry].[Hours Spent] = @Time, [tblEntry].[Comments] = @Comment WHERE (([tblEntry].[ID]) = @ID)", conn);
Query.Parameters.AddWithValue("@ID", m_ID);
Query.Parameters.AddWithValue("@Week", m_Week);
Query.Parameters.AddWithValue("@Year", m_Year);
Query.Parameters.AddWithValue("@Date", m_Date);
Query.Parameters.AddWithValue("@ProjectID", m_ProjectID);
Query.Parameters.AddWithValue("@ActionID", m_ActionID);
Query.Parameters.AddWithValue("@Time", m_Time);
Query.Parameters.AddWithValue("@Comment", m_Comment);
try
{
Query.ExecuteNonQuery();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
}
Code Snippet
The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.
What am I overseeing here?
--Pascal
View 13 Replies
View Related
