Linking Access to MS SQL Server

[BrianPaul]

Correct me if I am wrong or add further comment.

1. SQL server is basically where you store all your tables?

2. Can you also store querries on SQL server (dumb question sure you can)
or if using MS Access as front end store querries in Access Linking them to
tables on SQL Server. If using Querries on SQL server then do you link you
forms to the Queries on the server?

3. Can you write modules in SQL Server App like Access? like action querry
or delete table, etc?

4. I know you can use MS Access as front end for an SQL server database but
does SQL have its own forms reports, etc like Access? If not is there other
available besides Access? Im assuming Visual Basic, Dot Net, and maby others
such as C sharp ASP Server, Dream Weaver, etc?

Just thought I would ask before loading up Server 2003 and SQL Server 2003.
Im sure security is controlled through Active Directory or some security
setting in SQL Server?

5. Final Question: I would like to take a few tables I created or even
querries if I can and transfer them to SQL server and start playing. Is it
possible?

 

[Rick Brandt]

Correct me if I am wrong or add further comment.

1. SQL server is basically where you store all your tables?

Mostly. In some cases you will also have local (jet) tables.

2. Can you also store querries on SQL server (dumb question sure you
can) or if using MS Access as front end store querries in Access
Linking them to tables on SQL Server. If using Querries on SQL server
then do you link you forms to the Queries on the server?

"Queries on the server" would be SQL Server Views. You can link to Views just
as if they were tables, but they might not be editable. LOCal Access queries
can be made thatuse the linked Tables and Views as inputs. You can
additionally create Passthrough queries in Access which send their SQL
completely to the server for processing.

3. Can you write modules in SQL Server App like Access? like action
querry or delete table, etc?

These woud be Stored Procedures on the server. The language TSQL is used and
you can do much as you would in a VBA module in Access (though the structures
are not as easy to use).

4. I know you can use MS Access as front end for an SQL server
database but does SQL have its own forms reports, etc like Access?
If not is there other available besides Access? Im assuming Visual
Basic, Dot Net, and maby others such as C sharp ASP Server, Dream
Weaver, etc?

SQL Server is strictly the "engine". You need something else for the interface
and Access works very well for this. You could however use winforms in a dot
net app or use a web interface or VB, Delphi, etc..

Just thought I would ask before loading up Server 2003 and SQL Server
2003. Im sure security is controlled through Active Directory or some
security setting in SQL Server?

SQL Server has both "integrated" and "standard" security. The former assigns
permissions based on your network profile and the latter requires a specific
login to the server.

5. Final Question: I would like to take a few tables I created or
even querries if I can and transfer them to SQL server and start
playing. Is it possible?

Sure. I would recommend using the actual SQL Server client tools for creating
the objects rather than using the upsize wizard.

 

[BrianPaul]

Thanks alot helped me out a great deal...You did get me laughing so hard I
about choked to death when you mentiond TSQL. Have a hard enough time with
vba, modules in access.

 

[Arvin Meyer [MVP]]

Thanks alot helped me out a great deal...You did get me laughing so hard I
about choked to death when you mentiond TSQL. Have a hard enough time
with
vba, modules in access.

T-SQL is fairly intuitive if you can stumble around in VBA. I wouldn't be
intimidated, if I were you.

But you'll be able to do much more using custom VBA functions in queries
than T-SQL.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[dbahooker]

dont link jack shit

upsize to SQL Server.

Access Data Projects _GREATLY_ simplify access development.
I mean; it makes it a single tier; instead of 2 'fake tiers' (splitting
frontend - gag)

yes; SQL Server and Access Data Projects can use Active Directory.

Access MDB can't do jack shit and it is inherently an insecure
platform.

MDB is for babies.
ADP rocks.

Don't be a crackhead; use ADP.


-Aaron

 

[dbahooker]

Rick;

you are a PUSSY for not reccomend ADP. The kid is asking for Access
<-> SQL Server.

grow some fucking balls you retard.

Use your credibility for something worthwhile.

-Aaron
ADP Nationalist

 

[dbahooker]

and for the record; Access Data Projects makes sprocs that are just as
easy to use as MDB.

but when you 'stack queries' in MDB shit just randomly starts crapping
out; until you have to go back and rewrite a half-dozen queries just
because Access throws a tissy fit.

MDB is for babies.
..NET is -IMPOSSIBLE- for a newbie.

Use ADP; and you'll be done already.

-Aaron