SQL Backend/Access Frontend

[PC Datasheet]

I have zero experience with using a SQL database for a backend and Access
for a frontend. I have some questions:
1. Does an SQL database have tables?

2. How does Access connect to the data in an SQL database? Linking,
importing, or ???

3. Earlier today there was a thread regarding DAO and ADO. In the thread it
was said that ADO is very useful when the backend is a SQL database. Could
someone explain that?

4. Can an Access frontend update data in a SQL database backend? How?

5. Is there a website that provides answers to these questions?

Thanks for all input!!!

Steve
PC Datasheet

[strvariant]

"PC Datasheet" <no****@nospam. spam> wrote in
news:RF******** ********@newsre ad1.news.atl.ea rthlink.net:

I have zero experience with using a SQL database for a backend and
Access for a frontend. I have some questions:
1. Does an SQL database have tables?

2. How does Access connect to the data in an SQL database? Linking,
importing, or ???

3. Earlier today there was a thread regarding DAO and ADO. In the
thread it was said that ADO is very useful when the backend is a SQL
database. Could someone explain that?

4. Can an Access frontend update data in a SQL database backend? How?

5. Is there a website that provides answers to these questions?

Thanks for all input!!!

Steve
PC Datasheet

Hi Steve,

Some answers to your questions are:

1) Yes

2) You would link to the tables via ODBC. You can also import and export
using an ODBC link via Access. (TransferSpread sheet, TransferText)

3) ADO is useful when dealing with SQL data. However you can use DAO to
accomplish many tasks. I tend to use more DAO in my coding because I am
more familiar with it. This is an example:

'***Declare the variables
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSql as String

'***Set the variables
strSql = "SELECT * FROM tblTemp;"

'***CurrentDB is the Access database you are using
Set db = CurrentDb

'***Specify the recordset
Set rs = db.OpenRecordse t(strSql)

'***From here you would read the Recordset for values
'***and generate whatever you wanted, based on the results.

*Note---In order to use DAO the Access database must have a Reference to
a DAO Library (DAO360.dll, DAO351.dll, etc...). This is true for ADO too.
But don't try to use both at the same time. It WILL cause problems.
Choose one and stick too it.

4) An Access front-end can certainly update SQL tables. For example:

a) Link to a SQL table with an ID field.

(The ID Field can be anything but I have generally used
fields with a datatype of <int> for integer.)

b) Once this has been done you can use Access queries ---
(Append, Update, Delete) to change the data.

5) http://www.mvps.org/access/ is an excellent site to begin with

These are general answers and as many computer-folk will tell you there
are 100's of ways to do the same thing.

Hope these help!

strvariant

[Tom van Stiphout]

On Mon, 11 Apr 2005 20:33:35 -0500, strvariant <no.email.com > wrote:

Isn't it true that it's hard to work with stored procedures from DAO?
For example how would you call a sproc that returns an Output
parameter and capture that value? In ADO there are specific
provisions for that (the Direction argument to the CreateParameter
method of the Command object).

Steve, if you own Access2000 or up, you already own SQL Server. That
is, you own MSDE, the "light" version of SQL Server. File > New >
Project (New Database).

-Tom.

"PC Datasheet" <no****@nospam. spam> wrote in
news:RF******* *********@newsr ead1.news.atl.e arthlink.net:

I have zero experience with using a SQL database for a backend and
Access for a frontend. I have some questions:
1. Does an SQL database have tables?

2. How does Access connect to the data in an SQL database? Linking,
importing, or ???

3. Earlier today there was a thread regarding DAO and ADO. In the
thread it was said that ADO is very useful when the backend is a SQL
database. Could someone explain that?

4. Can an Access frontend update data in a SQL database backend? How?

5. Is there a website that provides answers to these questions?

Thanks for all input!!!

Steve
PC Datasheet

Hi Steve,

Some answers to your questions are:

1) Yes

2) You would link to the tables via ODBC. You can also import and export
using an ODBC link via Access. (TransferSpread sheet, TransferText)

3) ADO is useful when dealing with SQL data. However you can use DAO to
accomplish many tasks. I tend to use more DAO in my coding because I am
more familiar with it. This is an example:

'***Declare the variables
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSql as String

'***Set the variables
strSql = "SELECT * FROM tblTemp;"

'***CurrentDB is the Access database you are using
Set db = CurrentDb

'***Specify the recordset
Set rs = db.OpenRecordse t(strSql)

'***From here you would read the Recordset for values
'***and generate whatever you wanted, based on the results.

*Note---In order to use DAO the Access database must have a Reference to
a DAO Library (DAO360.dll, DAO351.dll, etc...). This is true for ADO too.
But don't try to use both at the same time. It WILL cause problems.
Choose one and stick too it.

4) An Access front-end can certainly update SQL tables. For example:

a) Link to a SQL table with an ID field.

(The ID Field can be anything but I have generally used
fields with a datatype of <int> for integer.)

b) Once this has been done you can use Access queries ---
(Append, Update, Delete) to change the data.

5) http://www.mvps.org/access/ is an excellent site to begin with

These are general answers and as many computer-folk will tell you there
are 100's of ways to do the same thing.

Hope these help!

strvariant

[Larry Linson]

"PC Datasheet" <no****@nospam. spam> wrote in message
news:RF******** ********@newsre ad1.news.atl.ea rthlink.net...

I have zero experience with using a SQL database for a backend and Access
for a frontend. I have some questions:
1. Does an SQL database have tables?

2. How does Access connect to the data in an SQL database? Linking,
importing, or ???

3. Earlier today there was a thread regarding DAO and ADO. In the thread
it
was said that ADO is very useful when the backend is a SQL database. Could
someone explain that?

4. Can an Access frontend update data in a SQL database backend? How?

5. Is there a website that provides answers to these questions?

Thanks for all input!!!

Steve
PC Datasheet

What's an SQL backend? If it's something like a Jet backend then it will
probably do everything OK.

[Larry Linson]

"Larry Linson" <no****@nospam. net> wrote

What's an SQL backend? If it's some-
thing like a Jet backend then it will
probably do everything OK.

Poor Donnie. His life must be awfully boring if he has to stoop to
impersonating ME again. Sad, Donnie, sad.

[Terry Kreft]

1. Yes

2. You can do this in a number of ways
a) Linked tables (using an ODBC datasource)
b) Access queries (using an ODBC datasource )
c) Pass through queries
d) In line code both DAO and ADO

3. Although ADO is a generic data interface it is optimised for SQL Server.

4. Yes, See 2 above

5. Lots of them search for
MS Access and SQL Server
--
Terry Kreft
MVP Microsoft Access
"PC Datasheet" <no****@nospam. spam> wrote in message
news:RF******** ********@newsre ad1.news.atl.ea rthlink.net...

I have zero experience with using a SQL database for a backend and Access
for a frontend. I have some questions:
1. Does an SQL database have tables?

2. How does Access connect to the data in an SQL database? Linking,
importing, or ???

3. Earlier today there was a thread regarding DAO and ADO. In the thread it was said that ADO is very useful when the backend is a SQL database. Could
someone explain that?

4. Can an Access frontend update data in a SQL database backend? How?

5. Is there a website that provides answers to these questions?

Thanks for all input!!!

Steve
PC Datasheet

[Bri]

Tom,

It most certainly can be done. Since a SP can have more than one
executable statement add on one at the end to return the parameter.
Personally, I like to have as much of the business logic in the frontend
as possible (much easier to send someone a new FE then to get access to
the server to add/modify a SP or view) so I use Passthrough queries. In
my example below, I modify the .SQL Property of a saved Passthrough
(setup for just this purpose) to include the input parameters I want,
then I run it and get the arameter in a Recordset. I don't see any
reason that the same results couldn't be had directly from a SP. BTW,
learned this trick right here in CDMA. :{)

simplified Code snip
=============== =====
stSQL = "SET NOCOUNT ON " & _ 'This is an important step to include
"INSERT INTO Signout (InventoryID,Em ployeeID,Signou tDate,Comment) " & _
"SELECT InventoryID,'1' ,'26-Jan-2005','' FROM InventoryAvaila ble " & _
"WHERE Inventory.ItemI D = 7 " & _
"SELECT @@RowCount as MyRowCount"

Set qd = db.QueryDefs("q ryTempPassthrou ghAction")
qd.SQL = stSQL
qd.ReturnsRecor ds = True
Set rs = qd.OpenRecordse t
MsgBox "Signed out " & rs!MyRowCount & " Items"

--
Bri

Tom van Stiphout wrote:

On Mon, 11 Apr 2005 20:33:35 -0500, strvariant <no.email.com > wrote:

Isn't it true that it's hard to work with stored procedures from DAO?
For example how would you call a sproc that returns an Output
parameter and capture that value? In ADO there are specific
provisions for that (the Direction argument to the CreateParameter
method of the Command object).

Steve, if you own Access2000 or up, you already own SQL Server. That
is, you own MSDE, the "light" version of SQL Server. File > New >
Project (New Database).

-Tom.

[shumaker]

In regards to #2. If you haven't tried this:
Access: File->New, Project(New Data)

You will be prompted to choose the SQL server and create a database.

Just about everything will be identical to using an access database
from the user stand point, but the data will be stored in the SQL
server database instead of an Access database file.

[billmiami2]

>>Personally, I like to have as much of the business logic in the
frontend
as possible (much easier to send someone a new FE then to get access to
the server to add/modify a SP or view)<<

How amazing. I find it much easier to alter a single stored procedure
than to send out fifty new user interfaces for every change. To each
his own.

Bill E.

[PC Datasheet]

What is the advantage to storing the data in an SQL server database?

Thanks,

Steve
<sh******@cs.fs u.edu> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

In regards to #2. If you haven't tried this:
Access: File->New, Project(New Data)

You will be prompted to choose the SQL server and create a database.

Just about everything will be identical to using an access database
from the user stand point, but the data will be stored in the SQL
server database instead of an Access database file.