InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 20:01:55 GMT20http://www.sqlservercentral.com/Forums/Topic1456375-131-1.aspxHi All,please help me on the below basic question:Is MS-Access db will keep the structure of index as b-tree?Thanks,Fri, 24 May 2013 04:32:14 GMTSQL*http://www.sqlservercentral.com/Forums/Topic1445509-131-1.aspxi query sql in MS Access 2007.i want find mix and max function with datetime.[b]This Code:[/b] [code="sql"]SELECT inf.SSN AS EmpNo, ck.CHECKTIME AS CHKDATE,Min(ck.CHECKTIME) AS TIMEIN, Max(ck.CHECKTIME) AS TIMEOUT FROM CHECKINOUT ck INNER JOIN USERINFO inf ON ck.Badgenumber = inf.Badgenumber WHERE (((ck.CHECKTIME)>=#20/03/2013#) Group By inf.SSN, ck.CHECKTIME ORDER BY inf.SSN, ck.CHECKTIME[/code][b]Result:[/b] [quote] EmpNo | CHKDATE | TIMEIN | TIMEOUT 1290005 | 20/3/2556 7:24:52 | 20/3/2556 7:24:52 | 20/3/2556 7:24:52 1290005 | 20/3/2556 19:07:54 | 20/3/2556 19:07:54 | 20/3/2556 19:07:54 1290005 | 21/3/2556 7:14:29 | 21/3/2556 7:14:29 | 21/3/2556 7:14:29 1320004 | 20/3/2556 7:28:57 | 20/3/2556 7:28:57 | 20/3/2556 7:28:57 1320004 | 20/3/2556 17:05:23 | 20/3/2556 17:05:23 | 20/3/2556 17:05:23 1320004 | 21/3/2556 7:15:30 | 21/3/2556 7:15:30 | 21/3/2556 7:15:30[/quote][b]But I want Result:[/b] [quote]EmpNo | CHKDATE | TIMEIN | TIMEOUT 1290005 | 20/3/2556 00:00:00 | 20/3/2556 7:24:52 | 20/3/2556 19:07:54 1290005 | 21/3/2556 00:00:00 | 21/3/2556 7:14:29 | 21/3/2556 7:14:29 1320004 | 20/3/2556 00:00:00 | 20/3/2556 7:28:57 | 20/3/2556 17:05:23 1320004 | 21/3/2556 00:00:00 | 21/3/2556 7:15:30 | 21/3/2556 7:15:30[/quote]How query this result in Access 2007. Thanks For your Time. ;)Tue, 23 Apr 2013 09:10:49 GMTppc493http://www.sqlservercentral.com/Forums/Topic1450388-131-1.aspxHi,I have an access 2010 adp linked to SQL 2008 r2.Another system we use which also has a sql backend hold employee id's and names . I want to use this employee data in our main sytem so I've set up a linked server - and a view based on this.I've given db users select permissions on the view but they're receiving the error message: "<access db name> could not log on to the server. Verify that the log on information is correct."Sql is set up to use windows authentication. The linked server is impersonating my log in.Any ideas on how to resolve this?Thanks,PeterTue, 07 May 2013 20:51:04 GMTpeterochttp://www.sqlservercentral.com/Forums/Topic917675-131-1.aspxHi.i was able "after a long time of trial and error" to migrate access database to sql 2005 and use linked tables to access the sql backend from the MS Access front end .MDB file. using upsizing wizard.all seemed to be fine. working remotely, local adding records.one thing is driving me crazy is that when we search against a table in access it takes long time.i ran the profiler on sql server and i see alot of "exec sp_execute'i am not very god in access . but on SQL i tried to create indexes"non clustered" on the fields we search by, and nothing..please point me in the right direction! please if any one can help please let me know...Thank youFri, 07 May 2010 01:22:40 GMTehamoudahttp://www.sqlservercentral.com/Forums/Topic1391676-131-1.aspxWhat is the easiest way to integrate Microsoft Access and Microsoft Word?We are converting permit numbers to QR code. 00000 – V / C 0000 – AT – YR Is this the easiest way?http://www.youtube.com/watch?v=ciPcmNv5rCwhttp://strokescribe.com/en/qr-code-excel-word-csharp-javascript.html Sat, 01 Dec 2012 16:06:30 GMTsasansamanihttp://www.sqlservercentral.com/Forums/Topic1404963-131-1.aspxThis is my sql query in access. However, I am not sure where I need to add or filter on District. District needs to equal 1.open attachment SELECT [Austin Police Department].Name, [Austin Police Department].Location, [Austin Police Department].[Phone Number], [Austin Police Department].District, [Austin Police Department].[Position Title]FROM [Austin Police Department]WHERE ((([Austin Police Department].Name) Like "*" & [Forms]![Austin Police Department- District 1]![txtSearch2] & "*")) OR ((([Austin Police Department].Location) Like "*" & [Forms]![Austin Police Department- District 1]![txtSearch2] & "*")) OR ((([Austin Police Department].[Phone Number]) Like "*" & [Forms]![Austin Police Department- District 1]![txtSearch2] & "*")) OR ((([Austin Police Department].District) Like "*" & [Forms]![Austin Police Department- District 1]![txtSearch2] & "*")) OR ((([Austin Police Department].[Position Title]) Like "*" & [Forms]![Austin Police Department- District 1]![txtSearch2] & "*"))ORDER BY [Austin Police Department].District;Wed, 09 Jan 2013 13:02:07 GMTsasansamanihttp://www.sqlservercentral.com/Forums/Topic1423662-131-1.aspxHello everyone,I have a project to make select queries from given database information but I was trying all day to make it and I just could not do it. I am writing here in this forum with hope that someone can help me with this project. Below are links to the Project Instructions and the database with the information. I have to submit the project tonight and I do not know what to do. I will appreciate if someone is wiling to help me with this.Thank you in advance. Instructions:https://www.dropbox.com/s/2naslcvdu9054tz/Project2_win13.docxDatabase Informationhttps://www.dropbox.com/s/229o5hrcga07t5i/Project2.mdbMon, 25 Feb 2013 09:27:09 GMTv.tomov90http://www.sqlservercentral.com/Forums/Topic1417320-131-1.aspxHi,I have experienced the following issue:Multiple users cannot open a .mdb file from shared drive at one time. We have situation if one user has alrealreadyned by double clicking this .mdb file, another user cannot open the same .mdb file by double clicking this file and don't receive any error messamessageease suggest me how to resolve this issue.Regards,GaneshThu, 07 Feb 2013 12:46:14 GMTGanesh Lohanihttp://www.sqlservercentral.com/Forums/Topic1399628-131-1.aspxWe've been using Workgroup for more years than I have toes. In Access 2010 when I use the DoCmd.RunCommand acCmdWorkgroupAdministrator I can create a new group or join an existing MDW file. I want to view the Access users and reset a few passwords. There must be an easy way to do this, but after trolling through the web for more than an hour I am stuck.Can someone PLEASE help?Fri, 21 Dec 2012 16:18:59 GMTfizzlemehttp://www.sqlservercentral.com/Forums/Topic1392179-131-1.aspxRun through the VBA code, SP removes no records.Run through the database queries window, SP removes no records.Run the SP through the Management Studio work exactly as designed.Not a complex SP.Any thoughts?Mon, 03 Dec 2012 16:31:18 GMTmwolfstonehttp://www.sqlservercentral.com/Forums/Topic1391462-131-1.aspxI am trying to send data from a form to a table. You click on a push button and then it sends the data to the table.Right now I am testing. I am trying to test if I can send data from one field on the form to a field in the table but its not working. What do you recommend me to do ?I am getting this error (Run-time error 3058). Index or primary key cannot contain a Null value.How do I increment the primery key automatically? Private Sub Command45_Click()Dim db As DAO.DatabaseDim rst As DAO.RecordsetDim varTextData As StringvarTextData = Combo6Set db = CurrentDbSet rst = db.OpenRecordset("Waste Hauler Number", dbOpenDynaset)rst.AddNewrst!Classification = varTextDatarst.Updaterst.Closedb.CloseMe!Combo6 = ""End Sub Fri, 30 Nov 2012 10:54:28 GMTsasansamanihttp://www.sqlservercentral.com/Forums/Topic1390273-131-1.aspxlets say I want to increment 0000 to 9999. I know how do this.how would you identify if I have used that number?How would you tell the field in the form to populate the numbers you haven't used?I guess my second question and third question are related.Wed, 28 Nov 2012 22:03:59 GMTsasansamanihttp://www.sqlservercentral.com/Forums/Topic1390311-131-1.aspxthe character is changed to a number when I send the info to a textbox.look at my design and my expression =[Combo0] & "- " & [Combo2] & "" & [Text27] & "- " & [Combo4] & "" & [Combo6] & "- " & [Text25]How do I need to build this expression?Thu, 29 Nov 2012 00:20:20 GMTsasansamanihttp://www.sqlservercentral.com/Forums/Topic1387444-131-1.aspxi am right clicking a non key field in my main form and filtering by a quote number. The key field projectID is tied to the subform by prjId. When i filter, it is not refreshing the subform. I am not seeing any records in the subform. However, when I open the main form it shows all the records in the subforms and i can navigate to the record. Here is the issue, in my sept 1 version there was no problem. In my oct 1st version something has changed and made this problem start. I have spent 6 hours analyzing both versions and cant find what could have changed. i feel like i have looked at all properties and relationships. What could cause this to happen?Wed, 21 Nov 2012 08:13:19 GMTbdog2607http://www.sqlservercentral.com/Forums/Topic1383422-131-1.aspxHi,Output in Access 2003:ID | [b]Description[/b] | Quantity | Title | Obj--- ---------------- --------------------------------------------------22| [b]6 [/b]| 253000.00 | |23| [b]7 [/b]| 330000.00 | |17| [b]1 [/b]| 340000.00 | 8414 | 6932718| [b]2 [/b]| 120000.00 | 8414 | 6934419| [b]3 [/b]| 615000.00 | 8414 | 6932720| [b]4 [/b]| 320000.00 | 8414 | 6932721| [b]5 [/b]| 809500.00 | 8414 | 69327Query :[code="sql"]SELECT TVFundBillDetail.ID , TVFundBillDetail.HID , TVFundBillDetail.Description , TVFundBillDetail.Quantity , TVFundBillDetail.Title , TVTitle.Name TitleName , Obj , TVAllObjects.Name ObjName FROM TVFundBillDetail LEFT OUTER JOIN TVTitle ON TVFundBillDetail.Title = TVTitle.Code LEFT OUTER JOIN TVAllObjects ON TVFundBillDetail.Obj = TVAllObjects.Code[/code]Problem: sequence of input row show in description column. when title and obj is null then displace sequence of input row. application is access 2003 and DBMS is SQL Server 2000Sat, 10 Nov 2012 23:26:45 GMTomid.shokrihttp://www.sqlservercentral.com/Forums/Topic1376758-131-1.aspxFront end MM Access adp file.On SQL server 2005 Form bound but user must not change data directlyUnbound text box to input new dataSave button to add record.Forms!some form!text box can't append...Tried Me.textbox won't work...Any advice?Wed, 24 Oct 2012 22:00:36 GMTsdhanpaulhttp://www.sqlservercentral.com/Forums/Topic1351922-131-1.aspxMy app includes 1) SS2K8 database on the back end2) Access 2007 front end NOT LINKED, data flows through ADO and stored procedures on the back end.=================Front End consists of forms/subforms and proxy tables "connected' to subforms. Data flows that way:Sql server table <===> Access proxy table <===> subform ==============Now a fun part begins:user opens the form (with continuous subforms populated with items) and just sits back doing nothing. Then suddenly records disappear from the subform with scary "=deleted" on the items a second ago having meaningful data. Can you imagine how happy the user can be?I checked the underlying table - empty==================After reloading the Access form all data comes back (SQL Server table was not changed)What? Why? No ideaValWed, 29 Aug 2012 15:07:44 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1356594-131-1.aspxHi guys, I'm not knowledgeable about XML or T-SQL but do you know whether it's possible to use syntax such as Arthur's revised query in Access?I know that Access can run stored procedures in SQL Server but can the above be considered a stored procedure?http://www.sqlservercentral.com/Forums/Topic1355591-21-1.aspxMon, 10 Sep 2012 02:45:48 GMTgrovelli-262555http://www.sqlservercentral.com/Forums/Topic1357952-131-1.aspxMy Access 2003 (or 2007) has linked tables to SQL Server 2008 (I'm using SQL Native Client 10). Forms, reports and queries are, naturally, local. Problem: all data nvarchar(max) fields are truncated - in Access the record is chopped - ~ 255 from the end of the record. Can it be fixed? And if yes then HOW?Wed, 12 Sep 2012 06:59:16 GMTvaleryk2000http://www.sqlservercentral.com/Forums/Topic1335447-131-1.aspxHi,I have a SSIS package which imports data from a source excel to the Sql Server database.I want this package to be triggered from Ms Access form button event.For this I have created a job and calling this job from a store proc, which has to be executed from button event.Am using following in my SPEXEC msdb.dbo.sp_start_job N'SSIS_FFIEC101';Not sure if this is correct!My requirement is:TO create a SP which executes the job and this SP has to be called from MS Access.I here also want to know whether the job has been executed completely.Any other suggestions, pls let me know.Any ideas pls help.Thanks,SriniWed, 25 Jul 2012 15:21:59 GMTranguhttp://www.sqlservercentral.com/Forums/Topic1324622-131-1.aspxI am in need of providing a query that gives a subtotal of a charge, the percent of total charges, the weight of an item, and the average weight of an item. I'm pretty sure I can get all of this info in subqueiries or multiple queries but I wanted to see if I could do it in one?table layoutcolumn names - chargeDescription, charge, weightExample data - wrong weight, $2.00, 20lbs wrong address, $5.00, 2lbs wrong address, $5.00, 3lbs wrong weight, $3.00, 10lbsMy resultset needs to be something similar to:ChargeDescription, sumOfCharges, PercentOfTotalCharges, averageWeightwrong address, $10.00, 66%,2.5lbswrong weight, $5.00, 33%, 15lbsAny help appreciated! Have a good 4th!Tue, 03 Jul 2012 12:58:30 GMTSQLNubehttp://www.sqlservercentral.com/Forums/Topic1317144-131-1.aspxHi Every one,I'm very new to this forum and access 2007 so please bare my questions.I have an application in my computer i often use it for retrieving data based on the requirements they already programmed some queries in that application, my problem is that i have one requirement which is not meets the already existing queries i have a query but that file extension is "accdr", ok i changed it to "accdb" when they asked and solving my requirement but i think this is not good idea to do this way all the times because that application is linked with ODBC database.so please help me with permanent solution...Thanks for you help in Advance.Ganesh NMon, 18 Jun 2012 00:15:34 GMTganeshnarimhttp://www.sqlservercentral.com/Forums/Topic1312656-131-1.aspxI have one final assignment before I can actually start enjoying my summer break. I've been trying to figure it out for the last 3 hours.If any one could give me some pointer I'd highly appreciate it!The assignment is:Find "namn" and "antalplatser" for "Rum" that has been used by all "Lärare" that is from one "ort"The table names are in swedish, but translated the question is: Find rooms that all teachers that were born in LA have held a lecture inMy professor hinted that I should use double negation, but still, I can't figure it out.The relationship diagram:http://imgur.com/CmFpRThu, 07 Jun 2012 10:36:50 GMTniklasoldebackhttp://www.sqlservercentral.com/Forums/Topic1304710-131-1.aspxHello,I have problem to run ms access application (.adp) under Win7 with MS office 2007.(or access runtime 2007)Database is placed on SQL2000 server.When I want to open some form, the same error message appier.(see attachment)Thank you.Wed, 23 May 2012 00:28:04 GMTpeter478http://www.sqlservercentral.com/Forums/Topic493264-131-1.aspxI previously posted help in trying to obtain a Virtual File Server / Virtual PC running Access 2003, and I've looked around, but I cannot find anything like this . (I did find a SQL 2005 one :D ) So, is there any compadibility issues with opending up an Access .mdb within Access 2007 and saving it as an Access 2003 .mdb? I appreciate it any help, thanks.Wed, 30 Apr 2008 14:09:31 GMTOP_HELPhttp://www.sqlservercentral.com/Forums/Topic1293182-131-1.aspxAnyone have any experience with connecting to Access 2010 databases using a Linked Server on a 64-bit SQL Server instance installed on the same 64-bit Windows 7 Enterprise computer?I am unable to replace the local Office 2010 Professional Plus 32-bit with 64-bit due to corporate standards, and thus cannot install the 64-bit version of the Access 2010 Database Engine (AKA ACE). I have installed the 32-bit version, but can't see it, even using a .UDL file. Anyone?Steve(aka sgmunson):w00t::w00t::w00t:Tue, 01 May 2012 08:29:18 GMTsgmunsonhttp://www.sqlservercentral.com/Forums/Topic1280757-131-1.aspxHi,Its been many years since I have used Access in anger. I have been working as a MS SQL database developer for the past seven years so the Access way of doing things is a bit alien.I have recently taken on some charity work that i am doing in my free time. It is a order tracking database that has a SQL 2005 express backend with an Access 2010 front end.One of the forms in the Access front end is a Pivot table that is based on a database view. In the PivotTable Tools menu there is the option to "Export to Excel". This should export the whole pivot table and data connections into an excel worksheet.Current whent he button is press, Excel opens but produces an error ssaying "Problems came up in the following areas during load: PivotTable." It also say that an error log has been generated and give a location but that location does not exist and neither does the log.I have mangaed to trace this problem down to the just of INNER JOINS in the view. When i alter the database view to select directlly from a single table i can export the Pivot table without issue but when i put the joins back in i get the same error. I have even tried create a view that selects its results set from the origional view (This means that the view that access refs does not contain any joins) but i still get the same error.I have had a look around the site and the internet but i cannot seem to find anything that would indicate that this was a bug or a known limitation. Has anyone else encountered this and is there a way to get it working?ThanksSteveTue, 10 Apr 2012 07:06:13 GMTsperry-750868http://www.sqlservercentral.com/Forums/Topic1276837-131-1.aspxI have an option group on my access 2007 form with a data type of bit on a backend sql server. The default value is 0 and labeled as "Pending" on the form. The alternate option value is 1 and labeled as "Closed" on the form. When I change the selection to "Closed" the buttons both show as empty. I have attached three images to demonstrate what is happening. The database is on a sql server 2008 server and the access database is linked to the database. Can anyone offer any suggestions as to why this might happen? Thanks.Mon, 02 Apr 2012 12:22:50 GMTkwoznicahttp://www.sqlservercentral.com/Forums/Topic1276728-131-1.aspxI have an access front end to a SQL Server 2008 backend. I developed the database and front end on my desktop first. The database works as planned. I then created the same database on a sql server that is on the network for other users to use. I also installed the SQL Server 10.0 Native client on the users computers who would be running the accdb file. When they run the accdb file they can only read the data, and they are constantly being prompted to login to the database. When they are prompted to login the server name is my computer hostname. Also when they choose the drop down lists they do not see the foreign key data the should appear. I also tried going to the External Data ribbon -Choosing ODBC database - It then asks me to export tablename to tablename - I click OK - Create a DSN - Login with the username/password I created - and then receive the attached error message. I don't believe this is the right approach as I am not trying to create a new table or export the database. How am I supposed to change the prompting issue so when they launch the accdb file they dont have to constantly change the server name and get prompted so frequenly?What could possibly cause the drop down lists to show now data, even though it works locally for me?Is the fact that they can only read the data related to the first two issues?Thanks. I appreaciate the help.Mon, 02 Apr 2012 10:10:40 GMTkwoznicahttp://www.sqlservercentral.com/Forums/Topic1275747-131-1.aspxHi thereI've got a customer with an application that's based on microsoft access 2003 and has a backend that stores data of sql 2008 r2. It has performance problems which I can confirm, but as I'm not an access specialist, here's my question: Is there something like settings to tune in ms access to speed up data access?Here's the background:- Database size is ~5 GB, SQL Server has 32 GB of memory- I've checked and corrected the obvious: missing primary keys, missing indexes- SQL Server is tuned as far as I know: Optimize for adhoc workload (97% is adhoc), data files, log files, backup files and tempdb are on different drives and raid arrays, sql server is configured to consume 28 GB of ram- SQL Server is running constantly at or below 5% CPU, disk IO is always close to 0, disk queue length is 0, sometimes 0.05. network IO is sometimes close to 25% outgoing, but normally 4-5%Here's the problem:If I check profiler some queries have up to 350000 ms to complete when coming from ms access. If I run the same queries again from ssms, I'm in the range of ~100 ms. Queries returns up to 20000 rows, which are total up to 3 MB in size. Even if I add some round trip times to the total time, I'm nowhere close to 350 seconds. I see also lots of ASYNC_NETWORK_IO waits which are coming from ms access.For me it seems that access cannot receive data fast enough, even if sql serevr could send it faster.Many thanks in advanceChristianEdit: I've forgot to mention that data access provides is SQL serevr native client 10Fri, 30 Mar 2012 07:39:53 GMTmickdundehttp://www.sqlservercentral.com/Forums/Topic1262314-131-1.aspxI am trying to create an application in access 2010 that will generate reports and save them as .PDF, using VBA. I'm not very well versed in Access and my question is, if I create a DB in MSA 2010 for this application will someone running MSA 2003 be able to view the DB/Application or would I have to make a work around for it to run on a MSA 2003 box? Essentially I know that there is a button on the ribbon in 2010 that will save it to a PDF, but I'm hard coding it into a button within the application and want to know if I'll have to use LEBAN's module for the 2003 boxes to accomplish this. I'd much rather not, because We'll all be switching over to 2010 in the coming months and Lebans workaround is rather hard to launch commercially. Thanks in advance,S.C.Tue, 06 Mar 2012 09:15:16 GMTshaynecl.areyhttp://www.sqlservercentral.com/Forums/Topic1251126-131-1.aspxHi,I need the top and prior to top records from a table for each country. below is my test data.can you pleaset tell me how we can get the desired result. In oracle we can get this using lead function, but in MS Access how to get ittable acol1 col2 col3us 01/01/2012 10us 02/02/2012 11us 03/03/2012 33gpb 01/01/2012 20gbp 02/02/2012 21chf 01/01/2012 30output should beus o3/03/2012 33 02/02/2012 11gbp 02/01/2012 21 01/01/2012 20chf 01/01/2012 30Thanks & regardsNaveenMon, 13 Feb 2012 05:20:40 GMTekknaveenhttp://www.sqlservercentral.com/Forums/Topic1256430-131-1.aspxCan any body help me..???i don't know Access very well, in my project we have one tool that was creating using ms access it was created by some one else now we are using that tools for retrieving the data from source depending on some input. now we want another way to finding the objects like that...please help me in this...!!!SELECT CABWO_CABLE_WORK_ORDERS.WO_NO, CABWO_CABLE_WORK_ORDERS.ISS_NO, CABWO_WO_DETAILS.TITLE, CABWO_WO_DETAILS.PLANNER_NAME, CABWO_SUPERVISORS.NAME, (CABWO_TECHNICIANS.NAME & " (" & CABWO_TECHNICIANS.TEL_OFF & ")") AS Sup_Name, CABWO_CONTRACTORS_N.CON_NAME, CABWO_CABLE_WORK_ORDERS.RECEIVED_DATE, CABWO_CABLE_WORK_ORDERS.PLN_START_DATE, CABWO_CABLE_WORK_ORDERS.PLN_COMP_DATE, CABWO_CABLE_WORK_ORDERS.ACT_START_DATE, CABWO_CABLE_WORK_ORDERS.ACT_COMP_DATE, CABWO_CABLE_WORK_ORDERS.STATUS, CABWO_CABLE_WORK_ORDERS.WO_COMMENTSFROM CABWO_WO_DETAILS INNER JOIN (((CABWO_CABLE_WORK_ORDERS INNER JOIN CABWO_TECHNICIANS ON CABWO_CABLE_WORK_ORDERS.TECH_CODE = CABWO_TECHNICIANS.TECH_CODE) INNER JOIN CABWO_SUPERVISORS ON CABWO_CABLE_WORK_ORDERS.SUP_CODE = CABWO_SUPERVISORS.CODE) INNER JOIN CABWO_CONTRACTORS_N ON CABWO_CABLE_WORK_ORDERS.CON_CODE = CABWO_CONTRACTORS_N.CON_CODE) ON (CABWO_WO_DETAILS.ISSNO = CABWO_CABLE_WORK_ORDERS.ISS_NO) AND (CABWO_WO_DETAILS.WONO = CABWO_CABLE_WORK_ORDERS.WO_NO)WHERE (((CABWO_CABLE_WORK_ORDERS.WO_NO)=[Please Enter WO Number]) AND ((CABWO_CABLE_WORK_ORDERS.ISS_NO)=[Please Enter Issue Number]));if we hit this querry it asks the work order number and issue if it matches it will come...in the same way i want the data as per "WO.TITLE" is it possible...????hope some one can help me...!!!Thu, 23 Feb 2012 00:16:38 GMTganeshnarimhttp://www.sqlservercentral.com/Forums/Topic1253634-131-1.aspxI've taken over a database admin role where one of the companies main applications is an access 2010 adp linked to sql server 2000 backend. On a data entry table where man hours and machine hours are entered there are vba sub routines firing off in the background - for example when the finish hours from the machine are entered the [TotalMachineHours] field on the machine details table is updated. What I'd like to do is get rid of the vba routines and have the job done by database triggers. I've a couple of questions on this: (a) is this good practice (b) if the access forms are updating the db tables via an updateable snapshot will this actually fire off the triggers (c) I want to go down the line of implementing merge replication so the timesheets can be entered on remote sites - how will the affect the database triggers? Will they still be actioned when the subcriber updates the main db?Any help or advice on this would be much appreciated.PeteFri, 17 Feb 2012 00:09:03 GMTpeterochttp://www.sqlservercentral.com/Forums/Topic1253954-131-1.aspxI have two tables ... a table table1 with KampanjID, Number and Date attributes.There kampanjID and Date is a composite key.Now I want to show TOTAL Number of per day.I use this SQL questionSELECT DISTINCT rKam.Datum, (SELECT SUM (Shipping) FROM rKam)FROM rKamAs shown each date once. BUT in the column where the number of the day, the TOTAL number of ALL days.Do I use this code instead:SELECT DISTINCT rKam.Datum, DeliveryFROM rKam;As shown dates as many times as there are entries for that date. (There may be various campaigns on the same date).I can really bet on this ...Anyone have advice how to solve so that the dates shown only once and in the second column so Plussa all the numbers together for that day REGARDLESS what the campaign.Or, you can not with the premise that I have a composite key in this table?grateful for the quick reply :-)/ NiklasEDIT: Clarification ... what I want to do is turn up all the dates are the same ... Then add up all the numbers on THESE lines and present this in the date's number ...O then so on the next date.Fri, 17 Feb 2012 08:52:45 GMTnigge_ehttp://www.sqlservercentral.com/Forums/Topic1251272-131-1.aspxI just started at a new company, so I am trying to find my way around there database and applications. I am having to do it on my own as they don't have the staff to sit with me, and the staff doesn't really to know what I need to find out anyway.So here's the deal. We have a MS SQL Server 2000 database (will be upgrading to 2008 R2 in a couple of weeks), we have an "Application" written in Access 2010. The main application references the MS SQL DB, but then we have these "Mini-Applications" which are more for Reporting rather than changing of data. These "Report Applications" use Access DB, the tables are obviously populated somehow from the SQL DB but when it was written items where not named the same, table structures are not the same. What I need to accomplish. I am supposed to be basically recreating their reporting in SQL Server RS 2008 (will get installed in a couple weeks with SQL 2008 R2), but I am not that savvy in Access (used it a few times 6-7 years ago but not to this extent.). I cannot figure out how to find what is populating the Access tables from the SQL tables. I have tried:1. Utilizing the MsysObjects and MsysQueries tables in Access I am trying to find anything referencing the tables in question, but am not quite finding what I am looking for. 2. I can go in design view of one of the reports and see the form that is being called, then I open the form and see what qry is being called, but that query doesn't tell me what populates the tables that are used in the query to create the report. There are many different Access DBs set up for the many different Access Reporting Applications, so some of the reports/dbs actually reference others, so the Create/Insert queries/procedures could be anywhere. I have tried to go through all of them with the Msys tables, but still nothing.If anyone can help me out or at least give me another option to try it would be much appreciated.Mon, 13 Feb 2012 08:55:19 GMTkschwidhttp://www.sqlservercentral.com/Forums/Topic657473-131-1.aspxI have an Access 2007 front-end to a SQL database via an ODBC connection. I also have a SQL stored procedure that inputs 4 parameters and builds a table/records that I want to feed back into an Access form.The Form/Property Sheet/Data/Record Source .... opens a Query Builder window. This window only allows "delete, insert, procedure, select or update" commands. I have been trying unsuccessfully to execute the SQL stored procedure. I always get a "syntax error in PARAMETER clause" regardless of how I code the Procedure statement. The Access help is pretty skimpy on the Procedure clause and I've tried everything.Any advice or alternatives (w/o programming please) would be appreciated.Sun, 15 Feb 2009 21:22:11 GMTKKinKChttp://www.sqlservercentral.com/Forums/Topic1245341-131-1.aspxI am using MS Access 2007.There are two tables each having a memo column.I have written a IIF statement to compare both these columns. If the values match exactly then the query returns the column from the first table. If the values do not match then it must suffix the column text with "Error:". Issue:the query returns correct results, however when I try to export the value into Excel 2007, then it trauncates after 255 chars.Please could you advise on how this can be fixed. This issu occurs only while exporting. In the query window the correct values are returned though !Wed, 01 Feb 2012 14:31:50 GMTnpranjhttp://www.sqlservercentral.com/Forums/Topic1246921-131-1.aspxMy dad has MS Access 2007 on his PC. I've got it on one of my desktops. He called me up, asking how to insert an image into a field of an Access table. Frankly, I've never done that, but whipped up a simple table, with an indentify (autonumber) field and an OLE Object field, and tried to insert images. I found that if I choose .jpg files, they got inserted into the field with a label of "Package", but if I inserted a .bmp file it got inserted into the field as a "Bitmap Image". More importantly, when I generated a quick report against that table, all of the "Package" items appear as broken images, but all of the "Bitmap Image" records had images in the report. So, clearly it looks to me as though they have to be .bmp files. I told my dad that, and he went and tried to do that. However, he claims that all of them get inserted as "Package", so none of them look right in reports.Why? What's he doing wrong? Is there some configuration setting he needs to take care of?Sat, 04 Feb 2012 10:11:59 GMTDoctor Who 2http://www.sqlservercentral.com/Forums/Topic1246072-131-1.aspxDear allI have a form with 10 Yes/No fields and below each column of 5 I count up all fields that have a tick in them. This works fine when I add Sendkeys "{F9}" for each field. I tick a field, F9 is sent and the calcualtion is done.Is there something that I can add to the form rather than each field which will update the form and re-calculate the count, insted of appying the F9 to each field. In the end, there will be about 50 Yes/No fields and I do not want to do 50 F9's.ThanksThu, 02 Feb 2012 13:18:10 GMTaa.nichol