SQLServerCentral / Microsoft Access / Microsoft Access

compatibility problem Win7

peter478 — Wed, 23 May 2012 00:28:04 GMT

Hello,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.

Access 2003 .mdb in Access 2007

OP_HELP — Wed, 30 Apr 2008 14:09:31 GMT

I 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.

Access Database Engine 2010 (32-bit) on Windows 7 x64

sgmunson — Tue, 01 May 2012 08:29:18 GMT

Anyone 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:

Export to Excel from Pivot Table in Access 2010

sperry-750868 — Tue, 10 Apr 2012 07:06:13 GMT

Hi,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?ThanksSteve

Option button not showing change

kwoznica — Mon, 02 Apr 2012 12:22:50 GMT

I 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.

ACCDB file using SQL Server Backend

kwoznica — Mon, 02 Apr 2012 10:10:40 GMT

I 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.

Performance tuning tipps

mickdunde — Fri, 30 Mar 2012 07:39:53 GMT

Hi 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 10

Access 2010 to Access 2003

shaynecl.arey — Tue, 06 Mar 2012 09:15:16 GMT

I 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.

query to get top 2 records

ekknaveen — Mon, 13 Feb 2012 05:20:40 GMT

Hi,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 & regardsNaveen

Help needed

ganeshnarim — Thu, 23 Feb 2012 00:16:38 GMT

Can 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...!!!

Updatable snapshot vs sql triggers

peteroc — Fri, 17 Feb 2012 00:09:03 GMT

I'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.Pete

Get numberofSales per day when date and number of sales are in the same table!

nigge_e — Fri, 17 Feb 2012 08:52:45 GMT

I 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.

MS SQL Server 2000 & Access 2010

kschwid — Mon, 13 Feb 2012 08:55:19 GMT

I 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.

Execute SQL stored procedure in Access

KKinKC — Sun, 15 Feb 2009 21:22:11 GMT

I 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.

Export memo field to Excel truncates values

nidhis — Wed, 01 Feb 2012 14:31:50 GMT

I 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 !

Having problems inserting a photo into a table

Doctor Who 2 — Sat, 04 Feb 2012 10:11:59 GMT

My 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?

Access form fileds update

aa.nichol — Thu, 02 Feb 2012 13:18:10 GMT

Dear 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.Thanks

Passing Criteria from a form to a Query in Access OCBD linked to SQL data

SQL33 — Tue, 20 Dec 2011 08:34:40 GMT

I have a 2010 MSAccess database using tables linked (ODBC) to a SQL 2000 database. I can query dates from the linked SQL table if I use the criteria syntax <#1/6/2009#. So my question then is .. how do I pass the date criteria in this format from a form text box to the criteria for the query. The method I've tried is to put =[forms]![formname]![fieldname] in the criteria field of the query (doesn't work). I'm assuming I need to pass the required # syntax around the date criteria value to the query criteria? I've tried variations of =#[forms]![formname]![fieldname]#... but this does not work. What am I missing? Thanks..

Compacting Access Hangs

Welsh Corgi — Fri, 06 Jan 2012 12:21:33 GMT

I'm trying to append some data to SQL Server from two tables in an Access Database and as soon as I enable VBA it's starts to compact but it just hangs.Any ideas?

Contingent graph in ms Access2007

peter478 — Thu, 19 Jan 2012 05:47:03 GMT

Hello,I created contingent graph in Access2007, that is created like form.(its standard way, that is offered)But when I want to create simple button for openning of this graph, it only shows simple data in form not in contingent graph.When I open this form directly it shows graph.It is for me strange and I dont know why this behaviour. Where can be a problem? or what to set up?Thanks!

Access 2007 to SQL 2008 Help needed

kwoznica — Sat, 14 Jan 2012 10:44:03 GMT

I am trying to create a simple help desk application using access 2007 as the front end but am falling short in with some concepts. I have attached images(please see attached images) of the acces form as well as sql code to run on your own sql server. I have two simple tables. A CaseData table and a Category table. The CaseData table will hold the marjority of the data and be on the main form in access but I want to have a combo box lookup list that points to the category table. when I put such an object in the access form I can pick the category but when I look in the casedata.category column referntial integrity is lost as the field only shows null in sql server. You will see this in the untitled4.jpg I have attached. I would like the casedata.category column to be updated with the correct number from the category.id column. The untitled5.jpg message shows the category options.what could I be missing? I have added a foreign key constraint on the casedata.category column to the category.id column but this doesn't seem to have taken any effect. Below is the sql code to create the database, tables, and user objects[code="sql"]CREATE DATABASE [GKHELPDESK] ON PRIMARY ( NAME = N'GKHELPDESK', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GKHELPDESK.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'GKHELPDESK_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GKHELPDESK_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)GOALTER DATABASE [GKHELPDESK] SET COMPATIBILITY_LEVEL = 100GOALTER DATABASE [GKHELPDESK] SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE [GKHELPDESK] SET ANSI_NULLS OFF GOALTER DATABASE [GKHELPDESK] SET ANSI_PADDING OFF GOALTER DATABASE [GKHELPDESK] SET ANSI_WARNINGS OFF GOALTER DATABASE [GKHELPDESK] SET ARITHABORT OFF GOALTER DATABASE [GKHELPDESK] SET AUTO_CLOSE OFF GOALTER DATABASE [GKHELPDESK] SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE [GKHELPDESK] SET AUTO_SHRINK OFF GOALTER DATABASE [GKHELPDESK] SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE [GKHELPDESK] SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE [GKHELPDESK] SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE [GKHELPDESK] SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE [GKHELPDESK] SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE [GKHELPDESK] SET QUOTED_IDENTIFIER OFF GOALTER DATABASE [GKHELPDESK] SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE [GKHELPDESK] SET DISABLE_BROKER GOALTER DATABASE [GKHELPDESK] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE [GKHELPDESK] SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE [GKHELPDESK] SET PARAMETERIZATION SIMPLE GOALTER DATABASE [GKHELPDESK] SET READ_WRITE GOALTER DATABASE [GKHELPDESK] SET RECOVERY FULL GOALTER DATABASE [GKHELPDESK] SET MULTI_USER GOALTER DATABASE [GKHELPDESK] SET PAGE_VERIFY CHECKSUM GOUSE [GKHELPDESK]GOIF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [GKHELPDESK] MODIFY FILEGROUP [PRIMARY] DEFAULTGOUSE GKHELPDESK;GOSET NOCOUNT ON;-- Create CaseData Table CREATE TABLE dbo.CaseData ( Id INT IDENTITY(100,1) PRIMARY KEY ,CaseDate DATE NOT NULL ,Customer VARCHAR (50) NOT NULL ,Category TINYINT NOT NULL ,CaseDescription VARCHAR (30) NOT NULL ,CaseStatus VARCHAR(7) NOT NULL ,ClosedDate DATE NULL ,Resolution VARCHAR (400) NULL );CREATE TABLE dbo.Category ( ID INT IDENTITY(1,1) PRIMARY KEY ,CatType VARCHAR (25) NOT NULL );GO--Add values to the Category TableUSE GKHELPDESKgoINSERT INTO Category (CatType)VALUES ('Equipment') ,('Email') ,('Asset') ,('Navision') ,('User') ,('Desktop') ,('Laptop') ,('Disaster Recovery') ,('Phone') ,('Citrix') ,('Software') ,('Hardware') ,('PDF') ,('Sales') ,('Server') ,('Security') ,('Database') ,('Printing') ,('Meeting') ,('Project') ,('Microsoft Office'); USE GKHELPDESKgoINSERT INTO CaseData(CaseDate, Customer, Category, CaseDescription, CaseStatus)VALUES ('01/07/2012', 'Keith', '17', 'Building GK Help Desk Database', 'Pending');--01/12/2012 recieved a mismatch error in access so changing this to INT datatypealter table CaseData Alter column Category INT --01/13/2012 trying to add referential integrity for a category drop down list in accessUSE GKHELPDESKGOAlter Table dbo.CaseData Add Constraint FK_Category_CatType Foreign Key (Category) References dbo.Category (ID);--01/14/2012 change the Case Status column to a BIT datatypealter table CaseData Alter column CaseStatus BIT[/code]Any suggestions are welcome.

Access ADP

Terry_Pino — Sun, 21 Nov 2004 22:49:00 GMT

Hi All,

I am new to ADP style databases and am wondering if it is possible for end users to manually insert records from an ADP style database as they would in a regular Access database that has linked tables. I have created a test ADP database using the database owner in the connection properties, yet the Insert New Record option seems to be disabled when the table is opened.

On the Advanced tab I cant seem to change any properties. Have I missed something or am I doing this wrong?

Thanks in Advance,

Terry Pino

How to call After update event to the textbox

pwalter83 — Tue, 20 Dec 2011 09:39:07 GMT

Hi,I have a added a code in the After Update event of a textbox which I need to call to display value in another textbox. This is what I have entered in the After Update event:------------------------------------Private Sub Text41_AfterUpdate()[Me].[Text45] = DLookup("DIVISION_CD", "dbo_DIVISION", "[LINE_CD] = '" & [Me].[Text41] & "'")End Sub------------------------------------Can some one pls tell what code or syntax I need to add in the text box control source to call the above After Update event ?Thanks,Paul

Passing parameters to SQL Store Procedure from Access from

satishchandra — Tue, 20 Dec 2011 15:03:51 GMT

Converted access 2007 DB to SQL server 2008 using SSMA. The access forms needs to be retained as front end and SQL Server as back end. Was able to create some static pass through queries, and was able to get data through ODBC link to SQL.One of the access forms has a drop down listing regions(approx 10 regions), and a start and end data fields.Prior to conversion, There are about 10 queries written, one for each region. A macro is called, which picks up the appropriate query based on the selected drop down region.The query has where conditions, against the date field values captured on the form. As part of conversion, moved the query to a store procedure and converted to SQL syntax. Was able to test this code, as pass through query, by hard coding values. How can i pass the form date values and drop down values, as parameters to the store procedure.looking for help on this.

DLOOKUP Syntax

pwalter83 — Tue, 20 Dec 2011 04:19:35 GMT

Hi,I am trying to autopopulate a Textbox45 from another Textbox41(already autopopulated) and have entered this code in the control source property of Textbox45-=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = ' " & [Me].[Text41] & " ' ")and added this code in the After Update Event property of Textbox41-Private Sub Text41_AfterUpdate()[Me].[Text45] = DLookup("DIVISION_CD", "dbo_DIVISION", "[LINE_CD] = ' " & [Me].[Text41] & " ' ")End SubBut I get an error- #Name? when I view the form to test it. Could someone please tell what I am doing wrong and if there is another way to do this ?Thanks,Paul

SQL Native Client with ms access

GrassHopper — Tue, 27 Dec 2011 12:28:19 GMT

I have an application that is currently using the older sql 2000 odbc driver to connect ms access to sql server. I wanted to try the newer SQL Native Client 9.0 ODBC Driver. I don't get an error msg but when I try to run a passthru query it kicks out without an error and does not run my passthru query on sql server. It kicks out of the function in the bolded line. the line it kicks out of in the function below has this value:sql = "SELECT MASTERREC,EMAILGUID,SentDate,ReceivedDate,Subject,ConversationID,ConversationTopic,LOCKED_BY_USER,RECID,AttachmentCount,"sql2 = ""sql3 = "Cast(' ' as Varchar(MAX)) as 'Comments',Cast(' ' as Varchar(255)) as 'Address' "sql4 = "Into [dbo].[tbl_KOHLS_EV_2010_abenit01] FROM tblv_EmailViewer WITH (NOLOCK) WHERE MasterRec ='177' Order by SentDate desc"I tried simplifying it by just saying "Select Masterrec from tblv_EmailViewer" and i get the same result. When i use the old odbc driver it works both ways.This is my connections string:"Driver={SQL Native Client};Server=ATL20AS1100SQ02;Database=AS_KHL_2010_2011;Trusted_Connection=yes;"function:Function SQL_PassThrough2(ByVal ConnectionString As String, _ ByVal sql As String, _ ByVal sql2 As String, _ ByVal sql3 As String, _ ByVal sql4 As String, _ Optional ByVal QueryName As String) Dim dbs As DAO.Database Dim qdf As DAO.QueryDef Set dbs = CurrentDb dbs.QueryTimeout = 300 Set qdf = dbs.CreateQueryDef With qdf .Name = QueryName .Connect = ConnectionString [b].sql = sql & sql2 & sql3 & sql4[/b] .ReturnsRecords = (Len(QueryName) > 0) .ODBCTimeout = 300 If .ReturnsRecords = False Then .Execute Else If Not IsNull(dbs.QueryDefs(QueryName).Name) Then dbs.QueryDefs.Delete QueryName dbs.QueryDefs.Append qdf End If .Close End With qdf.Close Set qdf = Nothing dbs.Close Set dbs = NothingEnd Function

Autopopulate textbox using sql table as look up table in MS Access form 2007

pwalter83 — Mon, 19 Dec 2011 07:53:10 GMT

Hi,I have a requirement to autopopulate a textbox (DIVISION_CD) based on the value in another textbox(LINE_CD). A table - DIVISION already exists which contains the 2 columns - LINE_CD and DIVISION_CD. How can I use the DIVISION table as a lookup table to autopopulate the value in the DIVISION_CD textbox based on the matching value in the LINE_CD textbox.Would somebody know on how to about this ? I have googled regarding this but have not been able to find any solution. Any suggestions would be appreciated.Further info:------------------Please find the table structure of the DIVISION table below:----------------------------------------------CREATETABLE [dbo].[DIVISION]([LINE_CD] [varchar](2)NULL,[DIVISION_CD] [varchar](4)NULL)ON [PRIMARY]----------------------------------------------The sample data for the above table:----------------------------------INSERT INTO DIVISIONVALUES('01', 'A432')INSERT INTO DIVISIONVALUES('02', 'A442')INSERT INTO DIVISIONVALUES('03', 'A452')INSERT INTO DIVISIONVALUES('04', 'A462')INSERT INTO DIVISIONVALUES('05', 'A472')----------------------------------The textbox - DIVISION_CD needs to be populated with the value based on the matching value of the LINE_CD textbox using the above table as a lookup table for looking up the values of DIVISION_CD column. I hope I am able to explain my situation.Thanks,Paul

Include condition in the combo box in a form (MS Access 2007)

pwalter83 — Fri, 16 Dec 2011 07:14:09 GMT

Hi,I have a requirement to include a condition in the combo box of the form which is as follows(SQL):[MG_SERVICE].[DELETED_FLG] = 'N' What I have done is right clicked on the combo box and chose - 'Build Event' and then in the Expression Builder added the following code:=[dbo_MG_SERVICE]![DELETED_FLG]='N'But it doesn't work and the combo still lists all the values. I only need to include the values where [MG_SERVICE].[DELETED_FLG] = 'N' Does someone know how to go about this ? Any suggestions would be appreciated.Thanks,Vishal

Excute SP in Access 2007 Database

tuhlig — Tue, 22 Nov 2011 15:25:47 GMT

I have an Access 2007 database that kicks off a sql stored procedure. The SP kicks off an email program. Runs fine most of the time. Once in a while the email program locks up.We think it because the Access db is still connected. How do I kill the connection in the code? Here is my code.Private Sub Command4_Exit(Cancel As Integer)Set con = New ADODB.Connection con.ConnectionString = "Provider=SQLOLEDB.1;Password=xx;User ID =XXXXXXX;Initial Catalog=XXXframework20;Data Source=XXX1SQl08" con.Open Set cmd = New ADODB.Command cmd.ActiveConnection = con cmd.CommandText = "INSERT INTO sv_process_event_tbl (process_guid,process_event_data_path, create_user) VALUES ('XXX52156X-X226-52X7-X781-X98X 6D85F569 ','\\XXFILESRV1\DATA\EmailXML.XML','CC2007_caccdb')" cmd.ExecuteEnd SubThanks in advance for any help.Tom

Access to SQL Server conversion

satishchandra — Wed, 14 Dec 2011 08:40:33 GMT

My client has a requirement to migrate from Access back end database to SQL server express. There are few reports and forms, and want to retain them.The report front end has date fields(from and to dates) and a region selection drop down.Once the data is migrated to SQL server, they want to fetch the data by passing the values from access front end to SQL server.I am a novice on Access, and What approach should be followed to acheive this.1. Should I use SSMA, to migrate database to SQL and link tables.2. In that case, how does the existing queries pass the selected values on access front to SQL Server. Would there any improvement on Query performance, specially when the data grows beyond 2 GB. 3. Can the SQL server database reside on Server A and have the access front end on Client Machine. I am assuming, the connection could be established through ODBC.

Creating A Simple Runnable Function in MS Access

Stylez — Tue, 06 Dec 2011 10:22:37 GMT

Good Morning Guys,i was wondering if i can do this piece of code programmatically on the query design of MS Access?DECLARE @TestVal int;SET @TestVal = 3;SELECT CASE 3 WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' ELSE 'Other'; END;if not how can i do it programmatically on MS Access??Thank you very MuchNoel

Insert into multiple rows

klc0000 — Fri, 04 Nov 2011 10:49:24 GMT

I have two tables an employee table and a training table. I know how to do a mass update but not really sure how to do a mass insert.Table1tblEmployeeEMPIDTable2 tblTrainingEMPIDCourseIDStatusBoth Tables are linked by the EMPID. Basically I want to write either in SQL or VBA/SQL something like this: For every EMPID in tblEmployee, insert a row in Table 2 that contains the EMPID, a CourseID of 9750, and a Status of "NT".I know how to update these rows once the row has been added like changing the Status to Q, but not how to add a completely new row.Thanks!

Updating records

palash.gorai — Fri, 04 Nov 2011 04:49:54 GMT

Hi, I am not getting it got to solve this. Single Ticket No is having two or more than two records, but displaying only at one record. I want to Update the Same Ticket No at the NULL areas just after it and before another Ticket Number starts.Source Data:------------------------------------TICKET NO BUSINESS AREA NET AMOUNT BASIC FARE AIRLINE TAX UATP COMMISSION1809828094 84270 59284 18400 0 2331 916 0 8001 0 1809828621 84140 61372 15200 0 2297 600 0 400 0 916 0 7949 0 1809829054 28202 11472 916 0 806 600 0 15400 0 620 0 -----------------------------------I need the RESULT in this manner:------------------------------------TICKET NO BUSINESS AREA NET AMOUNT BASIC FARE AIRLINE TAX UATP COMMISSION1809828094 84270 59284 18400 0 23311809828094 916 0 1809828094 8001 0 1809828621 84140 61372 15200 0 22971809828621 600 0 1809828621 400 0 1809828621 916 0 1809828621 7949 0 1809829054 28202 11472 916 0 8061809829054 600 0 1809829054 15400 0 1809829054 620 0 ----------------------------------regardsPalash Gorai

Ado recordset does not return value from varchar(MAX) field

valeryk2000 — Thu, 27 Oct 2011 09:11:56 GMT

Some records in this field have len = 22000. When I run the query (Select Summary From MyTbl Where ID=SomeiD) in ss2k8 - everything is ok. When I run this query from Access form intellisense above the MyRs(0) shows a set of small squares with occasional alfanumerics, and nothing can be seen in the appropriate textbox. When I change the query - Select Cast(Summary as varchar(8000)) From MyTbl Where ID=SomeiD everything is ok, if you do not count that the result would be truncated to 8000 characters (alas ....). Interestingly, if I use cast(Summary as text) - it works, but they say text datatype is being eliminated ...Any idea?

Data Import (Again)

Jim Mace — Thu, 27 Oct 2011 12:07:18 GMT

I know that importing data into an access database from SQL Server 2000/2005/2008 into Microsoft Access has probably been beaten to death many times over, but this is just a flat-out pain.I have absolutely no control over the computers that run my Access database, zero, yet, I need to be able to get out and grab data from my SQL Server 2008 database. Without fail, someone on some PC will come up with some ODBC connection error or something else that is totally obscure.This is essentially what I do. I have a form, and on a button click, it performs the following tasks: 1.) Gets the current DB using DAO.Database.CurrentDb (), 2.) Gets the connection string (stored in the Access database), 3.) Loops through all of the TableDefs putting its name into an array, 4.) Drops all of the data from the database tables using said array, 5.)Creates a temporary dbo_download_list table, 6.) Connects to my SQL Server 2008 database, 7.) Imports the data from dbo.tbl_download_list into a record set, 8.) For each record in that record set which contains table names, all of the data within those tables is dumped into a corresponding table in the Access database. 9.) Everything is cleaned up.At first, I was thinking about just grabbing data from a Web service, but in order to do this with Access 2007, you need to install some extra extensions and unfortunately I do not have that luxury. This works, when it works, but I need it to work reliably across multiple networks and topologies.I have my SQL Server setup on port 1633, so if this port is not open I can see some problems occurring when connecting. I was just thinking if there is a way that I could get away from instructing them to open a specific port by instead using a commonly open port like port 80. I am a web developer by trade, so I was also tossing around the idea of creating a webpage that just spit out the text file, then import the data that way, but I haven't been able to find any examples on how to do that with VBA.Any ideas would be greatly appreciated.Thanks,Jim

Conditional formatting in a form

peter478 — Tue, 11 Oct 2011 05:54:04 GMT

Hello ,I have a table displayed in a form in access .(data are stored in mssql2000)I need to mark " id " (in red) that meets condition.---------------------' sp1 (float) ' id (int) if condition is valid this field need to be marked in red. ' OK (int)' NOK(int)' storno (int)-------------------------------Table SO contain these fields: id sp1 OK NOK storno Text10 1 2.5 100 20 0 10.22 6.5 205 15 0 23 7.1 99 3 0 1.5 -------------------------------In form is created text10 field (from this field I call function that calculate every record from table, and result is float number e.g. 10.2)When I open this form i call code:Private Sub Form_Open(Cancel As Integer)Me.RequeryMe.RecordSource = "SELECT dbo.SO.* FROM dbo.SO WHERE (storno = 0)ORDER BY id DESC"'*********** this doesnt work********'If Me.sp1 > Me.Text10 Then Me.id.BackColor = RGB(255, 0, 0) End If'********************************'End SubThanks for your help!

Convert Access Attachment datatype to SQL Server 2005

Loner — Fri, 09 Apr 2010 07:46:27 GMT

I tried to convert an access database with one column of attachment datatype to SQL server.I used the SSMA tool from Microsoft to migrate the database but the attachment datatype became ntext datatype and had only the gif file name in that column instead of the real image.How can I convert the image of the attachment datatype to SQL Server with the image data type?Thanks

Switch() function in Access

renecarol33 — Wed, 21 Sep 2011 08:54:44 GMT

I'm supposed to write code that would give results of a truth table from a table in access. For example iif column_a <>"", column_b<>"", column_c<>"", column_d<>"", "1", Iif column_a<>"", column_b<>"",column_c<>"",column_d="","2", iif column_a <>"", column_b<>"", column_c="", column_d<>"","3", etc. There are 16 different combinations so I can't really do nested iifs. Would the switch function work. Or can I only switch 1 to 1 (there are 4 columns that I am checking values on).

Query to find matching rows

Chelo — Mon, 25 Jul 2011 14:18:51 GMT

hello- I'm working with a database that contains a table with IDs to create Relationships and Reciprocal records in the database. The table with the IDs for the Relat and Recip values look like this:UniqueID RelatID RecipID 21 1 222 2 153 3 854 4 755 5 996 6 997 7 4My goal is to find the "matching" rows, which in the example above would be Rows with the UniqueID 21, 22 (they "match" based on the RelatID/RecipID 1-2 and 2-1) and 54 and 97 (they matched on the RelatID/RecipID 4-7 and 7-4) Potentially, I would like to leave only 1 of the matching rows, so my final table would be: (eliminate rows with uniqueId 22 and 97)UniqueID RelatID RecipID 21 1 253 3 854 4 755 5 996 6 9any help will be greatly appreciated! thank you!!

Access 97 Error while connecting from SQL Server 2000

Sax — Tue, 30 Aug 2011 11:33:07 GMT

Hi,I have created a DTS packages that makes a connection between SQL Server 2000 and Access 97 db and imports data from Access 97. This DTS Package was scheduled using Jobs. This has been working from past 2 years. Suddenly in the last one month, when this package executed through the Job is failing, but when the package ran manually it is working fine. Any ideas why it is happening? The error from the job is below.Executed as user: IRI_CORP\AcxiomDBA. ...n OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: The Microsoft Jet database engine cannot open the file '\\xfp1\public\pub1\RIS$\PROD\RIS_APPS\vantive.mdb'. It is already opened exclusively by another user, or you need permission to view its data. Error source: Microsoft JET Database Engine Help file: Help context: 5003051 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8) Error string: The Microsoft Jet database engine cannot open the file '\\xfp1\public\pub1\RIS$\PROD\RIS_APPS\vantive.mdb'. It is already opened exclusively by another user, or you need permission to view its data. Error source: Microsoft JET Database Engine Help file: Help context: 5003051 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.Pls help as we have to run manually everyday the process.Thanks,