Newcomer to the whole milieu

Hello. I hope someone can help me here.

I work for a game company, and we have decided to drag ourselves
kicking and screaming into the 21st century by creating all our data in
a database. Since I have a little Access experience, I have been
nominated to Sort This Out. So, what I need to do is construct a
system of work whereby a database on our server (running SQL Server for
Team Foundation Server if that's relevant) can be queried and updated
from Access clients on the LAN.

To do this, I guess I need to know how to create a database, tables
within it, grant privileges for reading and writing, and export the
tables to another format. I can do nearly all of this in Access with a
local database - how different is it on a server?

Kind regards, and thanks in advance,

Guy Davidson.hatcat (guy.davidson@.hatcat.com) writes:

Quote:

Originally Posted by

I work for a game company, and we have decided to drag ourselves
kicking and screaming into the 21st century by creating all our data in
a database. Since I have a little Access experience, I have been
nominated to Sort This Out. So, what I need to do is construct a
system of work whereby a database on our server (running SQL Server for
Team Foundation Server if that's relevant) can be queried and updated
from Access clients on the LAN.
>
To do this, I guess I need to know how to create a database, tables
within it, grant privileges for reading and writing, and export the
tables to another format. I can do nearly all of this in Access with a
local database - how different is it on a server?

One thing is for sure, Access and SQL Server are very different. They both
use SQL, but there are great differences in the SQL dialects. In an Access
you can reference forms etc within a query, but not in SQL Server, because
it is a server.

I would recommend that you get some training, either by attending a class
or at least read some introductory book. Depending on how big the database
mission of your company is, you may also consider to bring in a consultant
to get you started. SQL Server is a complex tool, and it's easy to get lost.
That is, you may be able to compose an application on your own, but
performance and robustness may be so-so.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
I think you can build the front end in access( with forms), while
creating it, link the tables in SQL . you can update it through
access.
you should know about the tables inSQL.
If you are OK with access , it will be easy for you

BB

--
bisjom
----------------------
bisjom's Profile: http://www.dbtalk.net/m552
View this thread: http://www.dbtalk.net/t320512

Newcomer needs help with a SQL Query

Hi, I’m new to this list and looking for some help (my SQL is not brillian
t
to say the least) and information I’ve found on the web is confusing me
further.
It’s related to an SMS Web report, but I think it’s more of a SQL relate
d
question.
I am trying to pull a count of all specified programs in use in a collection
with a count of which machines they are installed on. I’ve had no problem
doing this. Query is as follows …
Select DISTINCT arp.DisplayName0, count(*) as 'Count', @.CollID as Collection
ID
FROM v_R_System sys
JOIN v_GS_ADD_REMOVE_PROGRAMS arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE (arp.DisplayName0 in ('3D+ 4.1 OEM Ed.')
or arp.DisplayName0 in ('3D+ 5.0 (AutoCAD)')
or arp.DisplayName0 in ('3D+ 5.2 (AutoCAD 2004)')
or arp.DisplayName0 in ('3D+ 5.2 (AutoCAD)')
or arp.DisplayName0 in ('3D+ 5.2 OEM Ed.')
or arp.DisplayName0 in ('3D+ ADT 2004')
or arp.DisplayName0 in ('3D+ ADT 2005')
or arp.DisplayName0 in ('3D+ V6.00 (AutoCAD 2004)')
or arp.DisplayName0 in ('Ward Toolkit')
or arp.DisplayName0 in ('West Point Bridge Designer 2005')
or arp.DisplayName0 in ('Xsteel 7.0','Xsteel 7.1','Xsteel 8.0')
or arp.DisplayName0 in ('XTRACT'))
and fcm.CollectionID=@.CollID
GROUP BY DisplayName0
ORDER BY Displayname0
Problem is that arp.DisplayName0 contains some duplicate entries (they are
not actually duplicates, there is a further column in the view called ProdID
0
and this is where the duplicate entries occur).
What I need to do is count only the first occurrence of each item in
arp.DisplayName0 for each computer and ignore the rest. I thought that SELEC
T
DISTINCT would do this, but it has no effect.
Can anyone help?
Thanks in advance …
________________________________________
________
Rob Stack MBCS CITP IEngPlease post DDL, sample data and expected results. Without that any attempt
at helping you is a guessing game.
Se this:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||>Select DISTINCT arp.DisplayName0, count(*) as 'Count', @.CollID as CollectionIDed">
>FROM v_R_System sys
>JOIN v_GS_ADD_REMOVE_PROGRAMS arp ON sys.ResourceID = arp.ResourceID
>JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
>WHERE (arp.DisplayName0 in ('3D+ 4.1 OEM Ed.')
>or arp.DisplayName0 in ('3D+ 5.0 (AutoCAD)')
>or arp.DisplayName0 in ('3D+ 5.2 (AutoCAD 2004)')
>or arp.DisplayName0 in ('3D+ 5.2 (AutoCAD)')
>or arp.DisplayName0 in ('3D+ 5.2 OEM Ed.')
>or arp.DisplayName0 in ('3D+ ADT 2004')
>or arp.DisplayName0 in ('3D+ ADT 2005')
>or arp.DisplayName0 in ('3D+ V6.00 (AutoCAD 2004)')
>or arp.DisplayName0 in ('Ward Toolkit')
>or arp.DisplayName0 in ('West Point Bridge Designer 2005')
>or arp.DisplayName0 in ('Xsteel 7.0','Xsteel 7.1','Xsteel 8.0')
>or arp.DisplayName0 in ('XTRACT'))
>and fcm.CollectionID=@.CollID
>GROUP BY DisplayName0
>ORDER BY Displayname0
Just a small aside, you can simplify that WHERE clause quite a bit by
actually using the IN test in a more conventional manner:
WHERE arp.DisplayName0
IN ('3D+ 4.1 OEM Ed.',
'3D+ 5.0 (AutoCAD)',
'3D+ 5.2 (AutoCAD 2004)',
'3D+ 5.2 (AutoCAD)',
'3D+ 5.2 OEM Ed.',
'3D+ ADT 2004',
'3D+ ADT 2005',,
'3D+ V6.00 (AutoCAD 2004)',
'Ward Toolkit',
'West Point Bridge Designer 2005',
'Xsteel 7.0','Xsteel 7.1','Xsteel 8.0',
'XTRACT')
Roy