Is This Possible? Please Help (MS Access Query Of Sql Database)
Jul 20, 2005
I work for an organisation that uses a bespoke document imaging system, the
database of which is an MS sql server.
We have MS Access and already use it for some querying of the database.
The database comprises a large number of distinct cases, which are
differentiated by case reference numbers, in one field (table?) of the
database. Each of these cases may have many documents associated with it,
denoted by the reference number, and these documents may be "new", "pending"
or "complete" shown in another data field.
We need to know how many cases have work outstanding on them.
Our problem is that our bespoke software will only count the number of
documents of each status, and not the cases.
Is it possible to design an MS Access query which will count the number of
different reference numbers which have any "new" documents associated, but
wont count each case more than once?
I am reasonably computer-savvy, I just don't know Access or SQL..
If I know it is possible, I don't mind putting in the effort to find out
how. I just don't want to waste time barking up the wrong tree ;-)
Of course any advice about how this would be achieved, such as pointers to
the right parts of the MS Access helpfiles, or to relevant websites would be
greatly appreciated. Some quick code would be even better...
Also, if there is any 3rd-party software which could easily do this, I need
help discovering it...I have looked long and hard, but don't know enough
about what I am looking for.
Yours in hope..
--
anthonyberet
Please reply in the groups, as my Usenet email address is not working at the
moment.
I have written a small app in VB2005 for a friend of mine and now I am trying to extract records for an access database with a specific month in the date field. What I am trying to get is all the entries for a month like July. I have this as my query:
SELECT ID, ShopDate, ShopText FROM Cost WHERE DATEPART([month], ShopDate) = ?
When I run the preview, it is asking me to pass along an AnsiString as a parameter. I have tryed numbers, spelling out the month, numbers in single and double quotes, spelled out month in single and double quotes but it will not take anything that I type as a parameter for the query
Ok, to start I will say I am a novice so detailed solutions are much appreciated. I believe I am on the right track. Here is what I am trying to accomplish. I have a page that uses an xml driven google map. When you click on the map marker, the info bubble displays brief information about a community and a link specified from the xml file. I have specified the links in the xml file to pass a query string variable ex: /community.aspx?name=uniqueCommunityNameHere. I tested this by simply placing a label on the community.aspx page that restated the "name" value - works fine (first commented out line of Page_Load). Now I need to complete my VB function that pulls the data record from the database - based on the parameter input from the query string - when the page loads. So if the user clicks the link on the marker for community1, it will populate community.aspx with info about community1 from the database... if they click community7 it will display that community info etc. Data Table: "Community" Data Fields: ID, Name, Description, Address, City, State, Phone, SalesRep. ASPX Page Design Elements: lblID, lblName, lblDesc, lblAddress.... etc. Here is my start... I will need help with the sql statement for sure - how do I create a where clause from the query string parameter passed in? Thanks so much for your help!
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load'My initial test to see that the query string is working lblName.Text = Request("name").ToString() ' Get the community info when loading community.aspx for the user selected communityIf Not IsPostBack ThenLoadCommunityInfo(Request("name").ToString())End IfEnd SubPrivate Sub LoadCommunityInfo(ByVal community)' Define data objectsDim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReader ' Read the connection string from Web.configDim connectionString As String = ConfigurationManager.ConnectionStrings("CONHTConnectionString").ConnectionString' Initialize connectionconn = New SqlConnection(connectionString)' Create commandcomm = New SqlCommand("SELECT * FROM Community WHERE Name = " & community, conn)' Enclose database code in Try-Catch-FinallyTry' Open the connectionconn.Open()' Execute the commandreader = comm.ExecuteReader()' Populate the list of community information -> Make the labels.text = the database fields --> lblName.text = table-Community field-"Name"lblName.Text = "Name"lblDesc.Text = "Description"....' Close the readerreader.Close()Catch' Display error messagedbErrorLabel.Text = "Error loading the community information!<br />"Finally' Close the connectionconn.Close()End TryEnd Sub
I am trying to create a report against an Access 2003 database using the provider as follows: "Provider=Microsoft.Jet.OLEDB.4.0;"
Simple reports with no parameters are fine (e.g. using a text query of 'Select * from BERs'
But how do I pass in a report parameter to limit the results coming back ? I have only used stored procs in the past which work fine but how to achieved this using Access has me stumped.
An example query I need would be 'Select * from BERs WHERE Year = [Year?]'
we want to completely do away with access, on my report tool,which is crystal report, we want query to be our data source so , we do not want to use ssis packageor import from access to sql, how can l handle bringing all table and the query from Access together.
We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?
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);
// //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);
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.
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
I developed a database with Access 2003 and everything was working good until my tech came in and reformated my hard drive and install a new Ghost image that met our company standards.
Now I cannot go in and make any changes to any of the tables, queries and forms. All of this started when a new Ghost image was installed on my pc.
The message I get when I try to open my database is "You do not have permission to run "tblSwitchboard." I get the same error message when I try to do anything at all on the database.
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
I have the following query created in MS Access that I would like to convert to MS SQL Server, but I can't get the IF logic to work properly, any help you can provide would be greatly appreciated.
Here is what I have, but I'm not confident it is correct: CASE WHEN (PTicketNum = '0' OR PTicketNum IS NULL) THEN 'M999Z' else PTicketNum END AS Ticket
We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
Dear All, i have a question abt winCE 4.2 and SQL server CE. i am using VB.net of Visual Studio 2005 My platform is using a PDA with winCE 4.2 and SQL server CE. The Host program is using dbf files on desktop side.
I got a problem of how to sync / read the sql CE data from a windows application.
so, i wanna ask,
1. any method to access the data from winCE data by windows application? or can i convert the sdf file to windows readable files? or any others?
2. Can i use a MDB to sync with SQL server CE? can i synchronize the mobile device which has a SQL Server CE database with the Access database on the desktop?
last question, 3. is that windows CE .net 4.2 not support pocket access (cdb) anymore?
I need detailed instructions on how to connect to a database from a Microsoft Access 2007 database to a Microsft Office Accounting 2007 database. The accounting database is an SQL 2005 datbase. It has an instance name of "MSSMLBIZ".
When I try I get an SQL error 53. Do not have permissions or database does not exist.
I'am doing functionality test on DTS packages and saving my DTS packages to meta data services instead of saving them as local packages. We would like to see what information would be provided by saving them this way, but when we try to open the meta data browser (the 3rd icon under DTS) we get the following error:
An error occurred while trying to access the database information. The msdb database could not be opened.
Hi I am wondering if anyone knows of a way of synchroniseing two versions of a database one in access and one in sql server so they can both run in parrallel in ssis for updates etc.
Also can anyone recommend a book which is easy to use or resources as I can't find what I am looking for in the online help.
I've used the ADS sample program and it works well. I looked at the code and changed it to VB (sorry, I'm not biligual yet), and have tried to 'adjust' it so I can connect to a remote PC (XP running IIS) instead of the local PC using the workgroup information database (system.mdw). Any ideas of the connection string that could make the connection?..my intention is to RDA once a week between a mobile device (3g/gsm) and a 'poor man's' IIS web server. I've tried everything I can think of, and I'm sure its possible since all I'm doing is changing the connection path.
I have a quick question regarding getting data from a SQL database but I am slightly confused as to the best way to handle it.
Basically on of the projects I am working on I need to send data to another company, there are several calls required to the database to bring back various options for changing the questions asked on the front end.
Up to now all that has happened is there has been a mutual agreement between myself and this other company and they have just had access to the database to call a series of stored procs which I have written for them to access the data. Recently however the situation has changed and my client wants me stop them accessing the database however they still need to recieve the information from the database they recieve now.
What will be the best way to handle this? My knowledge of SQL is farily limited presently and I am only ust getting into learning a lot more about it.
I have MS-Access as data source for one of the reports. I can preview the report fine from BI studio however, it does not work when I deploy it on report server.
The error is :
An error has occurred during report processing. Cannot create a connection to data source '<data source name>'.
The Microsoft Jet database engine cannot open the file '<UNC location of the MS-Access database>'. It is already opened exclusively by another user, or you need permission to view its data.
MS-Access database is located on a different server.
Any help to solve this? I understand it has something to do with permission both on server where reporting service is running as well as the server where MS-access database is located. Pls help.
I got thrown into a new project that is going to require me to update an SQL server database tables from an Access table on the backend of an Oracle database on another server. At the end of each day the Access dabase will be updated from the Oracle database.
What I need to do, is when the Access database is updated I need to have the table in the SQL database automaticaly updated. When a new record is added to the Access table I need the new record added to the SQL table. When a record is deleted in the Access table I need to keep that record in the SQL table and set a field to a value (such as 0). And when a record is updated in Access, have it updated in SQL.
Needless to say this is a bit out of my area and not sure how to accomplish this.
Hi, i am working with windows mobile Cf 2.0 and desktop. i got problem when i were trying to synd mobile database and ms-access database. my requirement is online i want to transfar data records from mobile to desktop vice versa. but how? i dont knwo any body would like to guide me. i am ready to accepting yours valuable views. waiting bye Rajat.
When I try to access the database diagrams in AdventureWorks, I get the following message:
TITLE: Microsoft SQL Server Management Studio Express ------------------------------
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
I was checking this site: http://www.aspfree.com/c/a/Microsoft-Access/Configuring-a-Linked-Microsoft-Access-Server-on-SQL-2005-Server/
and I do not know what parameters are neeed to do the following two. Configuring a Linked Microsoft Access Server on SQL 2005 Server - Linked Servers on SQL 2005 Server I would also like to know how to do it through this method as well.
Configuring a Linked Microsoft Access Server on SQL 2005 Server - Using the Management Studio to set up a Linked Server Sorry for the large typing I was using cut and paste to make sure I had everything correct.
I wanted to Synchronize the SQL Server (2005) Database to Ms-Access 2003. I want know how can I design the SSIS Package. Could anyone please advice me to design the package..?
$conn->Execute("INSERT INTO TblNews VALUES (20,'Testing', 'tekst', '16 Oktober 2006', 'Ikke')");
The 2nd insert command works perfectly but the first gives an error all the time! The table has a NewsID with auto_increment on..
The error: Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft OLE DB Provider for ODBC Drivers<br/><b>Description:</b> [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.' in d:www iedtzat.nlwwwieuw estdb.php:19 Stack trace: #0 d:www iedtzat.nlwwwieuw estdb.php(19): com->Execute('INSERT INTO Tbl...') #1 {main} thrown in site.com estdb.php on line 19
So if the user enters LocationCode 1, then he should only get G2 back.
I've started by trying to do a count query:
Code:
SELECT tblEquipmentGroups.GroupTagNumber, tblEquipment.LocationCode FROM tblEquipmentGroups, tblEquipment, tblLookUpGroups WHERE tblEquipmentGroups.GroupTagNumber = tblLookUpGroups.GroupTagNumber AND tblEquipment.TagNumber = tblLookUpGroups.TagNumber GROUP BY tblEquipmentGroups.GroupTagNumber, tblEquipment.LocationCode
But really I'm completely stuck. Any help/suggestions would be appreciated! Thanks, BB
I have a database front end using access, back end uses sql server. In front end, i have a query, how can i run this query in sql server. Another querstion, the cliend need the data, but that is too big, it is about 91700 records, How can i retrieve it and send to them. I try to use access, but when i save it as .xls file, it told me there is too many record to save it. Anybody has any idea about the data? Thanks in advance.
I've been using access to generate reports, queries... from progress db and now I need to switch to SQL. I realy need some help with converting my access statements into SQL. Here is statement I need help with:
LotStatus: IIf([pub_jobPhase].[warraid]="reg05" And [pub_jobphase].[Model]<>"_LotSale","Builder Available",IIf(Not IsNull([pub_jobsales].[finalsaledate] Or ([pub_jobphase].[Model]="_LotSale")),"Closed",IIf([description]=" ","Available",IIf(([sales status]="spec" Or [sales status]="model" Or [sales status]="ssbto" Or [sales status]="ssip" Or (Not IsNull([description]) And IsNull([pub_jobsales].[finalsaledate]))),"Allocated"))))
I know SQL doesn't have IF but CASE but I am stuck on multiple condition in one CASE and expecialy with if ... or... or (...and ...) type of statement!
