|
We have been using an Access 2000 mdb database with a VB.NET Windows application. We have recently ported from .NET 1.1 to .NET 2.0 and also upgraded to Office 2007.
Due to some of the users of our software, we have used Access for data storage instead of SQL Server, most of our clients did not want the server and liked having lots of smaller Access databases which they can look at outside of our software.
We are now looking at the option of either changing our database to an Access 2007 accdb format or even using SQL Server express which can be used on a users own machine or their server.
I have been looking on the Internet to find some stats on the performance benefits of either of these routes, particluarly to have a justified argument to change. Does anyone know what the performance benefits of an accdb database over an mdb database in terms of concurrent users, number of records that can be stored and general speed at higher volumes?
Also it would be good to know what else we gain over and above an accdb database using SQL Server express.
Any information or links to some stats would be much appreciated.
Jason
|
|
|
|
|
I don't know about the stats, but a great plus is that you have access to using Sql Server Management objects for working with database level requirements, such as creating tables and databases. Another plus that might not be useful immediately is the ability to create CLR stored procedures that can be debugged in VS2005 and use more common language functions.
We were using Access and ran into issues with performance and updatability of data within the database. We have since moved to Sql Express and I can tell you it is great being able to work with it instead of access.
Hope this helps at least a little...
Aaron
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
Hi, Is it possible to create a stored procedure using a text editor like note pad? I have three more questions for you.
If I can create it with a text editor, what extension do I give it and what folder must I put it in to make it work? <br />
<br />
If I can create it with notepad and I make changes to it, will SQL Server give error messages, or must I create a new stored proc? <br />
<br />
Is the syntax for MySQL be compatible with SQL?
The reason I ask is because if I can create a stored proc with a text editor, then it would really be convenient. If you know of a good tutorial on this please point me to it, thank you in advance for your help.
-- modified at 3:50 Wednesday 1st August, 2007
|
|
|
|
|
ASPnoob wrote: If I can create it with a text editor, what extension do I give it and what folder must I put it in to make it work?
Stored Procedures are stored internally inside SQL Server.
You should use the Sql Server Management Studio for 2005 or the Enterprise Manager for 2000.
You can create the SQL code in a text file and save it as a .sql file then import it into whichever sql management app you use but you might as well use the editor provided in the management app as it'll have more features than notepad :P.
ASPnoob wrote: Is the syntax for MySQL be compatible with SQL?
I assume you mean Sql Server ... SQL is a language, Sql Server is a database engine.
And kinda, if you use just the most basic of the Sql Syntax then it'll be ok but each database engine has it's own extentions. Sql Server uses T-SQL and MySql uses something I don't know about :P
|
|
|
|
|
Hi all
I have an issue with select statement. Like in one form i have 5 fields all combo box and after selecting all we have search option so in a data grid i am displaying the records which satisfies all criteria in the combo box. It works fine.
But now i want is if user select only one combo box and press search the grid should show the related records which satisfies the combo box condition. I have tried with AND OR condition in query but not getting the proper records.
Pl help
Regards
Prakash Mishra(Banglore,India)
|
|
|
|
|
There are several ways to solve this problem, I will suggest one of them here. If you have five parameters you need to set up your where clause to allow each of the parameters to be optional. All of this is done in the where clause. So the code would look like this if a parameter is being passed in then you need to set it to a default value. I will assume that all your parameters are strings.
Where (param1 = '' or param1 = @param1) and (param2 = '' or param2 = @param2) and (param3 = '' or param3 = @param3) and (param4 = '' or param4 = @param4) and (param5 = '' or param5 = @param5)
So the idea is if the user only selects one combo box, just set the rest of the parameters to '' and your query should work.
Hope that helps.
Ben
|
|
|
|
|
Thanks Ben i solved the problem of my own at last from morning i was trying and didn't got any reply. I am new to sql,
Regards
Prakash Mishra(Banglore,India)
|
|
|
|
|
I was waiting for reply from 5 long hours but didn't got a single one at last i solved the problem of my own. for for others reference i am posting the solution
select * from table name (must condition and 1st condition)or (must condition and 2nd condition)or (must condition and 3rd condition)....
Prakash Mishra(Banglore,India)
|
|
|
|
|
Hi when i parse this query i am getting sucesfull command...,
When i execute the query getting error like "Syntax error converting character string to smalldatetime data type"...,
Thsi is my procedure:
declare
@mthinc as tinyint,
@years as smallint,
@mth as varchar(50),
@yr as varchar(50)
set @mthinc=6
set @mth='@mthinc'
set @years=2007
set @yr='@years'
begin
select empid,max(edate) as edate from tblsalarydetails
where edate < @mth + '/01/' + @yr group by empid
end
I think to assign the tinyint & smallint value to varchar variable place i am getting error...,
How to solve that...,
Plz hlp me...,
Advance thanks,
Regards,
Magi
|
|
|
|
|
Hmm - this seems to be a little bit overcomplicated. Drop the varchar fields and change your where condition to
where edate < cast(@mthinc as varchar) + '/01/' + cast(@years as varchar) I really should point out here that you would be better off explicitly converting the condition into a smalldatetime to give the query optimizer a better chance of using an index on the column.
-- modified at 14:44 Wednesday 1st August, 2007
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I have Table called Customers with CustomerId as Primay key, FirstName and LastName. I also have a table called CustomerUpdates.
CustomerUpdates table has a foreign key of CustomerID. It also contains FirstName and LastName columns.
How can I write a query to update the FirstName and LastName in Customers table with
the FirstName and LastName in CustomerUpdates?
Please help.
|
|
|
|
|
Hi i think,
You must update one table at a time. You can update A while joining it with B to get the values you need.
ie:
UPDATE A
SET A.myField = B.newField
FROM A, B
WHERE A.primaryKey = B.foreignKey
Reply With Quote
OR try some thing like this
UPDATE CaveatsData
SET CaveatsData.Title = CavTitle.Title
FROM CavTitle, CaveatsData
where CavTitle.RecId = CaveatsData.RecId
Prakash Mishra(Banglore,India)
|
|
|
|
|
changing table name with code
hos
|
|
|
|
|
Sounds like fun.
I assume you regard this as a question. It's more like an appropriate subject line. What code ? What language ? T-SQL ? some other SQL dialect ? C# ? C++ ? LISP ? You get the idea.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
What database and version? What language?
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
If you are using SQL Server 2000, you would rename it using the sp_rename method. Suppose that you are renaming a table called MyTable to My_Table, you would run exec sp_rename 'MyTable', 'My_Table'.
It is important for you to be aware though that this is a very dangerous operation to undertake and you shouldn't do so lightly. When you rename an object like this you don't get any form of dependency checking and, as a result, anything that relies on the table being called MyTable will fail. Some areas that you would need to check include your stored procedures, views, functions. Don't forget to check your DTS packages as well - I've lost count of the number of times I've seen DTS packages failing because somebody renamed a table and didn't bother to update the mappings inside DTS.
Finally, make sure that you don't expose this functionality to the outside world. Do not, under any circumstance, allow any client code to do this - it WILL break things.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
If I have a query like
select bkID, count(*) from SalesLog where SaleDate = '20070410' group by bkID compute sum (count(*))
and if I had called it via a vb application is there any way I can get the value corresponding the sum?
|
|
|
|
|
hi all,
i m using Access 2007. if i create an query which use replace function and execute directly then it execute successfully in database .but if i run same query through application then it give error "Undefined function replace in expression".
i don't know where i am wrong ?
query is following
UPDATE master SET titles = replace(titles,'Trainer','Rupesh');
any body have idea or some suggestion ?
thanks in advance.
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
|
|
|
|
|
For security reasons, MS Access only permits a handful of functions to be called from an application outside of Access itself. I'm guessing that's the problem, but honestly, don't know if the "Replace()" function is on the forbidden-list.
Before I knew about this issue, I spent a lot of time writing VBA functions and using them within my queries, only to discover that none of the functions I wrote could be accessed from an outside application. Every query I called from the outside application that used one of my functions failed. I had to completely abandon the application and start over with an Access application.
Actually, I did do some googling looking for the list of functions allowed, but couldn't find it. Maybe some other reader knows where it is.
I don't understand how all this works, but try going to Access, Tools->Macro->Security, and select "Low" and see if that helps. If not, then I would restore the setting to it's previous setting.
David
|
|
|
|
|
Hi all
I am trying to use xp_SendMail from a stored procedure and it keeps failing with an error message about not specifying a parameter for @recipients when obviously I have specified it.
Sample
CREATE PROCEDURE<br />
...<br />
IF error GOTO ERROR<br />
...<br />
RETURN<br />
<br />
ERROR:<br />
exec master.dbo.xp_SendMail @recipients = "bla@here.com",...<br />
GO
If I move the xp_SendMail statement to between the Create procedure and Return statements (copy and paste) it works, it only does not work when it's in the 'ERROR:...GO' section. 
Anybody please...Deadlines are aproachin' 
Kiefie
The man with a plan. Now where'd that plan go?
|
|
|
|
|
So I have a table of teams, orgTeams, each of which has a primary key, teamID.
I have a table of users, orgUsers, each of which has field that describes what team the user is on, userTeamID.
If I want to get a list of all of the teams that have at least one person on them, it is easy:
SELECT DISTINCT teamID FROM orgTeams,orgUsers WHERE userTeamID=teamID
However, with people moving from team to team, sometimes teams end up "empty", i.e. with nobody at all on them. What query can I use to identify such "empty" teams?
|
|
|
|
|
To determine if a table has rows in it you can use
SELECT COUNT(*) FROM tableName
ie. ( I think)
SELECT DISTINCT teamID FROM orgTeams,orgUsers WHERE userTeamID=teamID and COUNT(orgTeams.Person) > 0
Kiefie
The man with a plan.
|
|
|
|
|
I understand the COUNT() function, but I don't understand what orgTeams.Person is supposed to refer to in your example. I don't have a "Person" field in my table orgTeams.
|
|
|
|
|
I don't know what fields you have in your table so I made it up. Could work with just about any field. I see there are more posts which will probably work better.
Kiefie
The man with a plan.
|
|
|
|
|
You could probably do this with:
select distinct teamId
from orgTeams
left join orgUsers
on teamID = userTeamId
group by userTeamID, teamID
having count(userTeamID) = 0
Deja View - the feeling that you've seen this post before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
