Access 2000 Queries Performing Slower On More Powerful Server
Jun 27, 2001
Hi,
We have recently upgraded our production server from a dual pentium II 400mhz server with 384Mb of RAM to a triple 700mhz Pentium III system with 2gb of RAM . However, since switching over, all of our Access queries on the SQL7 databases are either running extremely slowly or not at all despite the DSN's being set up correctly . Does anyone have any ideas why, and more importantly, how I can resolve this???
Thanks
Pete Burton
(IT Support)
Durham Aged Mineworkers Homes Association
View 2 Replies
ADVERTISEMENT
Jan 24, 2008
I'm using SQL2005. I'm confused on why queries run slower after I add indexes to a table. I thought the system would pick the best index available when running select statements.
sys.dm_db_missing_index* and Tuning Advisor keep recommending indexes that make my system slower. Maybe they are making a few queries faster that I'm not aware of but I do know they makes some too slow. Now I have to document which indexes to avoid creating for each table.
View 5 Replies
View Related
Aug 16, 2007
I am trying to a simple insert statement from a remote applicationagainst a sql server 2005 database. To fix the problem I was having,I had to grant the Login I was using the role of sysadmin. However Idon't want this user to have that kind of control, what would be thebest role to allow the user full access(including remoting) to onlyone particular database?
View 5 Replies
View Related
Sep 8, 2007
I'm writing an application for Windows Mobile 5 / Pocket PC using VB.NET 2005. The database is connected using an instance of SqlCeConnection and updated by an SqlCeCommand.
The application can perform select queries on data originally entered into the database through Visual Studio, or perform update / insert queries at run time. Anything inserted or updated can be returned by a select query whilst the application is running, however, anything I have inserted or updated doesn't appear to be written to the SDF file and hence is not in the database after restarting the application.
Am I missing something that's different between performing queries on an SQL CE database on Pocket PC and an ODBC source in a normal Windows application?
View 13 Replies
View Related
Dec 21, 2005
I have an Access 2.0 database that holds call data on a mapped drive. I am running MS SQL Server 2000. I can open it and view the records inside. I can even run the query below and get results, if I removed the CallDate and CallTime parameters.
SELECT CallDate, CallTime, Mid(CallRecordData, 68, 3) AS Extension, 'I' AS Direction, Mid(CallRecordData, 34, 11) AS Called,
Val(Mid(CallRecordData, 18, 2)) + Val(Mid(CallRecordData, 21, 2))/ 60 AS Minutes, Val(Mid(CallRecordData, 21, 2)) AS Seconds
FROM CallRecords
WHERE (CallDate = ?) AND (CallTime >= ?) AND (CallTime < ?) AND (Mid(CallRecordData, 30, 1) <> '9')
When I preview in the Transform Data Task, I get:
Package Error
Error Source: Microsoft JET Database Engine
Error Description: No value given for one or more required parameters.
When I look at the parameters, they are listed. I check their values, and they have the appropriate values (DateCalled, String, 07/14/2005) (StartTime, String, 06:30) (EndTime, String, 07:00)
When I run it in the build query or in Access with a linked table to the source, I can enter the values when asked for them and it works.
Thanks for any help you can provide.
View 2 Replies
View Related
Jan 29, 2007
Hello all - I have a SQL Server 2000 database setup using the Full Recovery Model. Each night, we backup the entire database, and as such would like to truncate the log at this time as well.
Is the best way to do this to also backup the Transaction Log, and then perform a DBCC SHRINKFILE command? It just seems like there should be an easier way...?
Thanks!
View 1 Replies
View Related
Jul 20, 2005
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies
View Related
Sep 20, 2006
Hi,
Using SQL Server 2000, I need to perform date range type queries that involve my company's Fiscal Year, which is not the same as the calendar year. My company's Fiscal Year if from Sept 1 to Aug 31, where Aug 31st year determines the Fiscal Year. For example, since today's date is 09/20/2006, the current Fiscal Year is 2007.
An example of a typical query requirement:
Find all the sales figures to-date for the current Fiscal Year. So, a WHERE clause will consist of a date range query from 09/01/2006 to 8/31/2007.
Initially, I created a Function to find the current Fiscal Year based on the current date, by calling the GETDATE() function and passing the results to the following function:
CREATE FUNCTION dbo.fnGetFY (@CurrentDatetime datetime)
RETURNS int
AS
BEGIN
DECLARE @FY int
IF (SELECT MONTH(@CurrentDatetime)) > 8
SET @FY = YEAR(@CurrentDatetime) + 1
ELSE
SET @FY = YEAR(@CurrentDatetime)
RETURN(@FY)
END
So, the view queries that involve the Fiscal Year call the above function.
However, these function calls drag down the VIEW query response time to the point where the time is either unacceptable or an ODBC Timeout occurs, even with Query Analyzer.
Is there a way to create a Global server parameter to hold the current Fiscal Year value, so function calls are not necessary? Or set Fiscal Year date ranges for a database or server system parameter?
Does anyone know of a efficent, response timewise, way to handle Fiscal Year date range queries?
Will appreciate the help!!!
View 5 Replies
View Related
Feb 19, 2004
I have a large VB 6.0 application running with Jet 4.0 Access DB. I am considering moving the DB to MSDE. For testing, I installed MSDE on another computer in a peer to peer network running XP sp1, loaded up the DB from Access to MSDE2000A -- all without problem. (I should add the obvious fact I am new to using SQL server.)
What I find is that with a relatively small test DB, running just a single instance of the application, query response from MSDE is taking several seconds (4-5 second lag) longer than response from the Access DB, which runs extremely fast. This is with the Access DB installed on the same network drive, running the same application and the same queries syntax -- only changing configuration of connection for each (SQL vs Jet 4).
Any clues as to what may be going on? The lag time is unacceptable. I am using SQL password instead of NT security. It seems the process is perhaps lagging in the process of authorization.
Thanks for any suggestions or ideas on this.
View 1 Replies
View Related
Mar 29, 2007
Hi there,
I am an application programmer who uses SQL Server; I'm not an expert and just know the basics. Our application has approximately 300 SQL tables and each table has just one primary index. We don't have stored procedures and only have 3 Views that a couple of reports use. Our database is approximately 26GB in size. We are planning on migrating from SQL 2000/Windows Server 2000 to SQL 2005/Windows Server 2003. Before doing this I decided to do some bench mark testing. I chose a simple SELECT statement on one of our larger tables. On SQL 2000 it ran in 22 seconds whereas on SQL 2005 it ran in 34 seconds.
These are the steps I have done to try and improve performance, all to no avail.
I tried both a passive and active upgrade and the results were the same. The passive way was to remove 2000, install 2005, create a new database and restore from a 2000 backup into the new 2005 database. The active way was to leave 2000 on the server with the database attached and upgrading to 2005.
I did not change any defaults on the database except I have set the compatibility level to SQL 2005.
The collation is set to SQL_Latin1_General_CP1_CS_AS.
I have run an Optimize Index Task.
I have run a Rebuild Index Task.
I have run an Update Statistics Task.
I have defragged the drive that the database resides on.
Can anyone explain why SQL 2003 is dramatically slower than 2000? Any help would be greatly appreciated.
View 20 Replies
View Related
Mar 29, 2007
Hi there,
I am an application programmer who uses SQL Server; I'm not an expert and just know the basics. Our application has approximately 300 SQL tables and each table has just one primary index. We don't have stored procedures and only have 3 Views that a couple of reports use. Our database is approximately 26GB in size. We are planning on migrating from SQL 2000/Windows Server 2000 to SQL 2005/Windows Server 2003. Before doing this I decided to do some bench mark testing. I chose a simple SELECT statement on one of our larger tables. On SQL 2000 it ran in 22 seconds whereas on SQL 2005 it ran in 34 seconds.
These are the steps I have done to try and improve performance, all to no avail.
I tried both a passive and active upgrade and the results were the same. The passive way was to remove 2000, install 2005, create a new database and restore from a 2000 backup into the new 2005 database. The active way was to leave 2000 on the server with the database attached and upgrading to 2005.
I did not change any defaults on the database except I have set the compatibility level to SQL 2005.
The collation is set to SQL_Latin1_General_CP1_CS_AS.
I have run an Optimize Index Task.
I have run a Rebuild Index Task.
I have run an Update Statistics Task.
I have defragged the drive that the database resides on.
Can anyone explain why SQL 2003 is dramatically slower than 2000?
Any help would be greatly appreciated.
View 1 Replies
View Related
Mar 29, 2007
Hi there,
I am an application programmer who uses SQL Server; I'm not an expert and just know the basics. Our application has approximately 300 SQL tables and each table has just one primary index. We don't have stored procedures and only have 3 Views that a couple of reports use. Our database is approximately 26GB in size. We are planning on migrating from SQL 2000/Windows Server 2000 to SQL 2005/Windows Server 2003. Before doing this I decided to do some bench mark testing. I chose a simple SELECT statement on one of our larger tables. On SQL 2000 it ran in 22 seconds whereas on SQL 2005 it ran in 34 seconds.
These are the steps I have done to try and improve performance, all to no avail.
I tried both a passive and active upgrade and the results were the same. The passive way was to remove 2000, install 2005, create a new database and restore from a 2000 backup into the new 2005 database. The active way was to leave 2000 on the server with the database attached and upgrading to 2005.
I did not change any defaults on the database except I have set the compatibility level to SQL 2005.
The collation is set to SQL_Latin1_General_CP1_CS_AS.
I have run an Optimize Index Task.
I have run a Rebuild Index Task.
I have run an Update Statistics Task.
I have defragged the drive that the database resides on.
Can anyone explain why SQL 2003 is dramatically slower than 2000? Any help would be greatly appreciated.
View 5 Replies
View Related
Jul 20, 2005
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
View 15 Replies
View Related
Mar 13, 2013
I am trying to get the same results in SQL server with a query as it works in access
There are two tables:
TITLES
Fields in query: TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel
Fields in Table:
[TitleID]
,[TitleNum]
,[TitleState]
,[VehYR]
,[VehMake]
,[VehModel]
,[NewORUsed]
[code]...
How this can be ONE SQL Query?In query designer on access theleft table is TITLES_IN_OFFICES_qry and the table on the right isTITLOC.I have never used a query as a table before in a select .
View 4 Replies
View Related
Apr 6, 2000
Does anyone out there know of any utility that can Migrate Queries to SQL Server 7. I have over 400 of them and Dread doing them all by Hand.
Thanks,
-Ray
View 2 Replies
View Related
Nov 2, 2000
I have a query in Access that has an iif statement in the select system like so:
SELECT [01_qryCommonCost_01].*, tblPercent_Afe.AfeDescription,
tblPercent_Vendor.[Vendor%], tblPercent_Afe.[Afe%], IIf([manual%] Is Not
Null,[Manual%],IIf([PropType]="NonMkt",0,IIf([Vendor%] Is Not
Null,[Vendor%],IIf([Afe%] Is Not Null And [GlType]<>"OpExp",[Afe%],[Gl%]))))
AS [%], 0+round2([Amount]*[%]) AS Allocated,
0+round2([Allocated]*[Salvage%]) AS Salvage
FROM (01_qryCommonCost_01 LEFT JOIN tblPercent_Vendor ON
([01_qryCommonCost_01].GlType = tblPercent_Vendor.GlTyp) AND
([01_qryCommonCost_01].VendorName = tblPercent_Vendor.VendorName)) LEFT JOIN
tblPercent_Afe ON ([01_qryCommonCost_01].AfeNo = tblPercent_Afe.AfeNo) AND
([01_qryCommonCost_01].Group = tblPercent_Afe.Group)
But I can't figure out how to nest the iif statement in T-SQL. Any hints?
View 2 Replies
View Related
Jan 22, 2008
Hi All,
I hope I am at the right place to post this question:
How can I move the tables with its data from Access to SQL Server 7.0 using SQL queries. Later I might put everything in the store procedure and have a third party running the store-procedure to do it by itself.
Please advice what should I do first? Do I create new tables in the s SQL Server 7 or can I move the data and create the tables at the same time?..Thank you.
View 1 Replies
View Related
Jan 13, 2008
Is there any log that catches all querys in SQL Server 2000???Full problem (in slanish): http://foro.hackhispano.com/showthread.php?t=28980Thanks
View 2 Replies
View Related
Mar 1, 2008
hi, friends, please look at this:
I have a SSIS package, and inside it I do something like below:
1. I have a SQL component, to give back a object to store the records.
2. I have a VB script component, I direct the object I got in 1 step into the script as a dataset.
My problem is:
I run the package in the SQL SERVER 2005 Store Procedue like this:
do
dtexec.exe package.dtsx
loop untill i>t
I control the it runs 30 times. But I found that the speed is slower and slower.
the first time, it takes about 600 s, but the last time, it takes the 1800 s.
Why?
The package don't drop the object it create during the loop in the Store Procedue ?
Thanks!
View 11 Replies
View Related
May 8, 2006
Hi,Does anyone know if MS SQL Server 2000, will run stability on a WindowsXP based O/S, using one of the new Dual Core chips, or HT chips?Especially given that when you install it you get compatabilitywarnings?ThanksDavid
View 2 Replies
View Related
May 3, 2007
Hi There,
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Thanks a lot!!
View 4 Replies
View Related
Jul 23, 2005
Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike
View 4 Replies
View Related
May 27, 2008
Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000
Number of instances per server
n/a
n/a
16
16
Number of databases per instance / server
n/a
32,767
32,767
32,767
Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647
Number of users per database
n/a
16,379
16,379
16,379
Number of roles per database
n/a
16,367
16,367
16,367
Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB
Number of columns per table
255
1024
1024
1024
Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage
Number of bytes per row
(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB
Number of columns per query
255
4,096
4,096
4,096
Number of tables per query
32
256
256
256
Size of procedure / query
64 KB
250 MB
250 MB
250 MB
Number of input params per procedure / query
199
1,024
2,100
2,100
Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB
Depth of subquery nesting
50
32
32
32
Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)
Number of columns per index
10
16
16
16
Number of characters per object name
64
128
128
128
Number of concurrent user connections
255
32,767
32,767
5
View 1 Replies
View Related
Jul 23, 2005
I've created a small company database where the tables reside in a SQLServer database. I'm using Access 2000 forms for a front end.I've got a System DSN set-up to SQL Server and am using links withinAccess 2000 to get to the SQL Server tables.My forms worked fine until I made a few minor changes to the databaseschema on SQL Server (e.g. added a foreign key, or added a column).After that, all the links break - I click on a table link and get anerror msg like "invalid object name."Deleting the links after a schema change and re-adding the links seemedto fix the problem. The forms I'd already created seemed to work fineafter re-creating the links.But then I got more advanced with my forms. I have it set up so thatfor certain entry fields, the combobox gets populated with values froma table (the description appears in the drop-down and the correspondingprimary key value gets populated in the table). I created a number offorms using this technique, entered data, and everything worked fine.Made a small schema change and it broke everything -- not the actualtable links, but the functionality for the drop-downs. My values nolonger appeared, and this was true for forms that accessed tables whoseschemas did not change.This is driving me nuts. Is there any way to keep my forms frombreaking each time I make a small schema change?Thanks.- Dana
View 5 Replies
View Related
Dec 13, 2007
I am not very good in queries. Could you please suggest me some web site/Tutorial/Artical where i can get Study Material for complex and real life queries. I know the syntexes, I just need to practice queries to enhance my skills
View 1 Replies
View Related
Jul 20, 2005
I changed from Access97 to AccessXP and I have immense performanceproblems.Details:- Access XP MDB with Jet 4.0 ( no ADP-Project )- Linked Tables to SQL-Server 2000 over ODBCI used the SQL Profile to watch the T-SQL-Command which Access ( whocreates the commands?) creates and noticed:1) some Jet-SQL commands with JOINS and Where-Statements aretranslated very well, using sp_prepexe and sp_execute, including thesimilar SQL-Statement as in JET.2) other Jet-SQL commands with JOINS and Where-Statements aretranslated very bad, because the Join wasn´t sent as a join, Accesscollects the data of the individual tables seperately.Access sends much to much data over the network, it is a disaster!3) in Access97 the same command was interpreted wellCould it be possible the Access uses a wrong protocol-stack, perhapsJet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server orJet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead ofJet to ODBC and ODBC direct to SQL-ServerDoes anyone knows anything about:- Command-Interpreter of JetODBC, Parameters, how to influence thecommand-interpreter- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server applicationThanks , Andreas
View 6 Replies
View Related
Oct 18, 2004
Hi,
I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.
Thanks
View 1 Replies
View Related
Mar 13, 2001
We have a SQL Server 7.0 system in NT 4.0 environment. We upgraded our users to Access 2000 and started to work with this. Now we installed a new
server which is Windows 2000 based and the domain is different from
the SQL servers domain. We then installed Access 2000 on Windows 2000
to use with terminal server. But I noticed that there was a problem
with the program. I then looked at the program which was written on Access 2000 and saw that the tables and views can't be seen. The program runs but
I can't see the views and tables. Another thing is access disconects from SQL Server when I want to see the tables. So what can be the problem.
In one part there is an Access 2000 on Windows 2000 server. On the other part SQL Server 7.0 on Windows NT 4.0. And Access can't see the tables in SQL server.
View 1 Replies
View Related
Jul 20, 2005
We are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.
View 1 Replies
View Related
Nov 21, 2006
Cannot see the Colums in the "design view" of Queries. All i see when i want to design a new query is *columns
This happens in only one database, in other databases using same server i can see the colums and can tick them to view then in the query.
In enterprise manager i see all the columns.
Using SQL 2000 and MSAccess 2000
View 1 Replies
View Related
Mar 15, 2004
I am converting a MS Access 2000 project to use SQL Server. This project has uncovered a number of problems, my latest seems to be very odd.
When I query using ADO against the SQL Server database, the results don't seemed to be returned immediately. It's like the access methods being used are waiting too long to write/read the data.
This could be a simple ADO configuration error, but I cannot find any settings that would make this behave so strange.
I'm using OLE/DB drivers with trusted security and attaching using client-side recordsets (I tried server-side as well, same results) to get data for the Access form. In many instances, the data from the form is not yet retrieved when I check the results using the debugger. If I execute the same section of code just seconds later, it works without fail.
I ran the SQL Server Profiler just for grins and found that records were not getting written to the database as I would have expected. Apparently ADO had generated a transaction and rolled it back. (why I don't know) I have looked in Microsoft's support database and on MSDN to no avail. Rather then rewriting my T-SQL as stored procedures for these functions, I'm hoping for some insights from anyone else who has been down this nasty road.
Here is the exact scenario:
Problem #1:
A form that accepts data and has an update button does not update the recordset being used to populate the form. I even coded a rst.Update statement into the code. The record within the recordset isn't updated until the DoCmd.GotoRecord , , acNewRec call has been made.
I suppose I could code the insert statement myself, but shouldn't Access be updating the database for me. (it does have r/w access)
Problem #2:
A subform that performs a query and allows records to be added to a table does not correctly detect data returned from the query. A check is made in the code for a field (which has a value of 0) but the code detects a null value. If I step through this code in the debugger, enough time passes that the value is present and I never see the problem. If I set a breakpoint at the error, the null condition is indicated, even though the debugger shows a value of 0 for the field.
SQL Server version is 2000, with SP3a applied.
I updated the MDAC to 2.8, JET to 8.0 and still the same problem. I cannot find anything in my ADO or Access books for a setting that would cause this behavior. My connection is using the following info:
Set Conn = New ADODB.Connection
Conn.CursorLocation = adUseClient
Set rst = New ADODB.Recordset
Connection string: Provider=SQLOLEDB;Data Source=myserver;Initial Catalog=mydb;Trusted_Connection=Yes;Integrated Security=SSPI;
Recordset Cursor type: adOpenDynamic
Recordset LockType: adLockOptimistic
I open the recordset using:
rst.Open mytable, Conn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
Problem # 1
After manipulating the data in the form, I press a "save" button on the form. This button calls a number of subroutines, eventually executing the code below:
rst.Update
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Problem # 2
I set the recordsource using: (in the form_open)
Me.RecordSource = "Select * from mytable where myfield = 'data'"
In the form_unload, the problems occur. It tests for a value stored in the form from the query. This value is null at the time of the code execution, it has a value when inspected within the debugger however.
Maybe I'm missing something here, this project was originally coded using DAO. My conversion to ADO is 95% complete. (all of these two components are converted to ADO) This problem is causing me to miss out on sleep and look like a rookie.
Has anyone seen or heard of something such as this? Is there any hope for this snarled mess of 25K lines of code?
Thanks!
View 3 Replies
View Related
Jul 20, 2005
Hi,I have developed an application using VB 6 (SP 5), MS Access 2000 andCrystal Reports 9. I migrated from from access to SQl Server 2000.This wasn't a problem as i could import all the tables to SQL Server.Also, i could make VB6 talk to SQl Server.The problem arsies when i run my application. The sql syntax foraccess seems to be different than that for SQL Server. A simpleexample being: In access boolean datatype is true/false ,whereas inSQL Server the boolean equivalent is bit (numerical 1 or 0). Thesekind of issues are causing problems and most queries don't run.Would i need to go and change all the queries in accordance with SQlServer syntax ,which would be very time consuming or is there anyfunction which will convert the access datatype into its equivalentSQl Server datatype??Any input/thoughts/suggestions would be appreciated.ThanksJatin
View 1 Replies
View Related
Jan 2, 2008
If you put an index on an integer type column named 'test_column' in a table that had 1,000,000,000 rows in it, and you said select top 50 * from test_table WHERE test_column = 1 since 'test_column' has an index, that would perform extremly fast wouldn't it? Cheers
View 5 Replies
View Related
