By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,855 Members | 1,988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,855 IT Pros & Developers. It's quick & easy.

SQL Backend/Access Frontend

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
42 Replies


P: n/a
"PC Datasheet" <no****@nospam.spam> wrote in
news:RF****************@newsread1.news.atl.earthli nk.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. (TransferSpreadsheet, 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.OpenRecordset(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
Nov 13 '05 #2

P: n/a
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****************@newsread1.news.atl.earthl ink.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. (TransferSpreadsheet, 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.OpenRecordset(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


Nov 13 '05 #3

P: n/a

"PC Datasheet" <no****@nospam.spam> wrote in message
news:RF****************@newsread1.news.atl.earthli nk.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.

Nov 13 '05 #4

P: n/a
"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.
Nov 13 '05 #5

P: n/a
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****************@newsread1.news.atl.earthli nk.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

Nov 13 '05 #6

P: n/a
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,EmployeeID,SignoutDate,Comment) " & _
"SELECT InventoryID,'1','26-Jan-2005','' FROM InventoryAvailable " & _
"WHERE Inventory.ItemID = 7 " & _
"SELECT @@RowCount as MyRowCount"

Set qd = db.QueryDefs("qryTempPassthroughAction")
qd.SQL = stSQL
qd.ReturnsRecords = True
Set rs = qd.OpenRecordset
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.


Nov 13 '05 #7

P: n/a
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.

Nov 13 '05 #8

P: n/a
>>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.

Nov 13 '05 #9

P: n/a
What is the advantage to storing the data in an SQL server database?

Thanks,

Steve
<sh******@cs.fsu.edu> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.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.

Nov 13 '05 #10

P: n/a
strvariant <no.email.com> wrote in
news:Xn***********************@216.196.97.136:
*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.


I'm not sure what you're saying here. If you mean that you can't
have references to more than one version of DAO in an MDB, then,
yes, you are correct.

If, though, you are saying (as it reads to me) that you can't have
both ADO and DAO references, you are quite wrong. It is best when
you do that to disambiguate your code, though, e.g.:

Dim rsDAO As DAO.Recordset
Dim rsADO As ADODB.Recordset

Certain things don't need to be disambiguated, such as Database
variables (which exist only in DAO), but I think it's a good idea to
do so, anyway.

I never use ADO, myself, but I always disambiguate my references
nowadays, just in case ADO comes into the picture some time in the
future.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #11

P: n/a
"Terry Kreft" <te*********@mps.co.uk> wrote in
news:y5********************@karoo.co.uk:
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


You know, this is the kind of top-posting that makes me crazy.

Exactly what is accomplished by avoiding inline answers to the
questions asked?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #12

P: n/a
On looking at this more closely, I suspect that Donnie first impersonated
Steve on the original post and then impersonated me on "my" response. It is
sad that Donnie has nothing better to do than try to make people look bad.

Larry

"PC Datasheet" <no****@nospam.spam> wrote in message
news:RF****************@newsread1.news.atl.earthli nk.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

Nov 13 '05 #13

P: n/a
Tom, we used passthrough queries against stored procedures in various server
databases even with Access 2.0 to obtain the "next id" for tables. I don't
have available a copy of that database to review _how_ we obtained the
return value, but clearly we did. This was to avoid Access losing track of
records created on the server with the eqivalent of AutoNumber.

Larry Linson
Microsoft Access MVP

"Tom van Stiphout" <no*************@cox.net> wrote in message
news:r9********************************@4ax.com...
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****************@newsread1.news.atl.earthl ink.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. (TransferSpreadsheet, 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.OpenRecordset(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

Nov 13 '05 #14

P: n/a
To All,

Ignore the post that seems to be from Larry. It's probably from troll Don.

Steve
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Larry Linson" <bo*****@localhost.not> wrote in message
news:UCX6e.14446$ox3.5223@trnddc03...
On looking at this more closely, I suspect that Donnie first impersonated
Steve on the original post and then impersonated me on "my" response. It is sad that Donnie has nothing better to do than try to make people look bad.

Larry

"PC Datasheet" <no****@nospam.spam> wrote in message
news:RF****************@newsread1.news.atl.earthli nk.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


Nov 13 '05 #15

P: n/a
On Tue, 12 Apr 2005 17:22:50 GMT, Bri <no*@here.com> wrote:

Hi Bri,
That's not quite what I had in mind. Here is a simplified example:
create procedure test1(@intOutput Output)
as
@intOutput = 5

Now call this sproc using DAO, and get the return value. Your
recordset approach will not work.
As Larry said, there is a way, but I don't know what it is.

-Tom.
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,EmployeeID,SignoutDate,Comment) " & _
"SELECT InventoryID,'1','26-Jan-2005','' FROM InventoryAvailable " & _
"WHERE Inventory.ItemID = 7 " & _
"SELECT @@RowCount as MyRowCount"

Set qd = db.QueryDefs("qryTempPassthroughAction")
qd.SQL = stSQL
qd.ReturnsRecords = True
Set rs = qd.OpenRecordset
MsgBox "Signed out " & rs!MyRowCount & " Items"


Nov 13 '05 #16

P: n/a
Thank you to everyone for all your time!!

I appreciate everyone's input. I got a good learning starting point from you
all.

Steve
PC Datasheet
"PC Datasheet" <no****@nospam.spam> wrote in message
news:RF****************@newsread1.news.atl.earthli nk.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

Nov 13 '05 #17

P: n/a
PC Datasheet wrote:
What is the advantage to storing the data in an SQL server database?


Off the top of my head...

It won't corrupt like Jet for one :-)
Database size is not limited.
Data type limitations, e.g. varchar(8000) or nvarchar(4000) compared to
Jet's text(255), ultimately better for sorting/grouping than a memo.
Security, can be SQL Server or integrated windows.
Client/Server architecture better for networks, low bandwidth areas,
internet/vpn, etc.
Transaction log aids robustness with automatic rollback/rollforward when
recovering databases at startup (try pulling the plug on the server of
both Jet and SQL Server then see which recovers and which needs
repair/compact/sending to Peter Miller, etc).
Index tuning wizard is quite handy.
Ability to cluster servers for larger installations.
Hetrogeneous database support.
2005 will have SFT via a failover server.
Triggers (stored procedures that run on update/insert or delete of a
table/view), better than form_afterupdate as a different front-end can't
bypass it.
Supports large number of simultaneous users.
Scalability, runs on anything from a Pocket PC to multiprocessor server
clusters.

enough for now? :-)

--
[Oo=w=oO]

Nov 13 '05 #18

P: n/a
Firstly the create procedure statement would be more like
create procedure test1
@intOutput int Output
as
SELECT @intOutput = 5
But having said that, I'd create a passthrough query with SQL as follows as
a first thought,
DECLARE @out int

EXEC test1 @out output

SELECT @out
--
Terry Kreft
MVP Microsoft Access
"Tom van Stiphout" <no*************@cox.net> wrote in message
news:eq********************************@4ax.com...
On Tue, 12 Apr 2005 17:22:50 GMT, Bri <no*@here.com> wrote:

Hi Bri,
That's not quite what I had in mind. Here is a simplified example:
create procedure test1(@intOutput Output)
as
@intOutput = 5

Now call this sproc using DAO, and get the return value. Your
recordset approach will not work.
As Larry said, there is a way, but I don't know what it is.

-Tom.
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,EmployeeID,SignoutDate,Comment) " & _
"SELECT InventoryID,'1','26-Jan-2005','' FROM InventoryAvailable " & _
"WHERE Inventory.ItemID = 7 " & _
"SELECT @@RowCount as MyRowCount"

Set qd = db.QueryDefs("qryTempPassthroughAction")
qd.SQL = stSQL
qd.ReturnsRecords = True
Set rs = qd.OpenRecordset
MsgBox "Signed out " & rs!MyRowCount & " Items"

Nov 13 '05 #19

P: n/a
You know, this is the kind of bottom posting that drives me crazy.

Exactly what is accomplished by posting pointless comments at the end of a
message?

--
Terry Kreft
MVP Microsoft Access
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn*********************************@24.168.12 8.78...
"Terry Kreft" <te*********@mps.co.uk> wrote in
news:y5********************@karoo.co.uk:
<SNIP>
You know, this is the kind of top-posting that makes me crazy.

Exactly what is accomplished by avoiding inline answers to the
questions asked?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 13 '05 #20

P: n/a
Bri
bi********@netscape.net wrote:
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.


If I were onsite as an employee, I would think the same as you. However,
I'm an independant consultant and I have no access to the Server unless
I make an appointment with the Server Admin, drive across town, make the
change, drive back.... I would rather make the change to the FE, e-mail
the changed object to the SA and have him Import it into the base copy
of the FE and then have the users get a new copy of it. Also, IMNSHO,
having all the logic in one place makes it easier to troubleshoot a
problem than to have it in two places.

--
Bri

Nov 13 '05 #21

P: n/a
Bri
Tom,

OK, I did some experimenting (damn curiosity :{) )and came up with:
CREATE PROCEDURE tmp
AS
SELECT 5

Then in Access I created a Passthrough query with ReturnsRecords=True:
Execute tmp

This returns a single record with a single field with a value of 5. The
field name seems to be undefined (displays as Expr1000), but it works.

I went on to try multiple return values:
CREATE PROCEDURE tmp
AS
SELECT 5, 10, 15

The query then returned a single record with three fields (Expr1000,
Expr1001, Expr1002).

To complete this I bound this query to a DAO Recordset variable:
Function testProc() As String
Dim lOne As Long, lTwo As Long, lThree As Long
Dim stSQL As String, stOutput As String
Dim rs As DAO.Recordset, db1 As DAO.Database

Set db1 = CurrentDb()
Set rs = db1.OpenRecordset("qryTempPassthroughAction")
stOutput = "One: " & rs(0) & " Two: " & rs(1) & " Three: " & rs(2)
testProc = stOutput

End Function

Calling this function returns:
One: 5 Two: 10 Three: 15

Is this more what you had in mind? With ADO you can execute a SP
directly into an ADO.Recordset variable. With DAO, you need a
Passthrough query. The Passthrough query can be modified on the fly (by
changing the .SQL property) to either the T-SQl of a SP (logic in FE) or
to Execute the appropriate SP (logic in BE).

--
Bri

Tom van Stiphout wrote:
On Tue, 12 Apr 2005 17:22:50 GMT, Bri <no*@here.com> wrote:

Hi Bri,
That's not quite what I had in mind. Here is a simplified example:
create procedure test1(@intOutput Output)
as
@intOutput = 5

Now call this sproc using DAO, and get the return value. Your
recordset approach will not work.
As Larry said, there is a way, but I don't know what it is.

-Tom.


Nov 13 '05 #22

P: n/a
Bri,

I'm also an independent consultant as well and I work mostly at home.
However, I have access to the network and a range of SQL Servers at
each client, whether this be via Citrix Metaframe, VPN or a little web
interface that I install and use as a last resort to run T-SQL
commands.

At one client, even the IT staff works remotely. Some work from home
and some from remote offices, thousands of miles from the servers.

I'm surprised in this day and age that your clients don't provide you
with better access and that you have to get in your car and start
driving. You should certainly bring this up with your clients.

Bill

Nov 13 '05 #23

P: n/a
Bri


Larry Linson wrote:
Tom, we used passthrough queries against stored procedures in various server
databases even with Access 2.0 to obtain the "next id" for tables. I don't
have available a copy of that database to review _how_ we obtained the
return value, but clearly we did. This was to avoid Access losing track of
records created on the server with the eqivalent of AutoNumber.

Larry Linson
Microsoft Access MVP


Larry,

From the SQL Server Books Online:
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

This identity value is then available using any of the methods I
described in my reply to Tom earlier in this thread.

If adding records to a recordset you can get the value of the Identity
field BEFORE you Update (unlike an Access Autonumber which becomes
available AFTER you Update):
rs.AddNew
rs!field1="some data"
lIdentity=rs!MyIdentityField
rs.Update
--
Bri
Nov 13 '05 #24

P: n/a
Bri
bi********@netscape.net wrote:
Bri,

I'm also an independent consultant as well and I work mostly at home.
However, I have access to the network and a range of SQL Servers at
each client, whether this be via Citrix Metaframe, VPN or a little web
interface that I install and use as a last resort to run T-SQL
commands.

At one client, even the IT staff works remotely. Some work from home
and some from remote offices, thousands of miles from the servers.

I'm surprised in this day and age that your clients don't provide you
with better access and that you have to get in your car and start
driving. You should certainly bring this up with your clients.

Bill


Bill,

I do have TS access at one client and it makes life a lot easier. With
there app, I am keeping the logic in the FE because they are considering
a change in their server DB form SQL Server to whoknowswhatorwhy for an
other app.

Also, I am much more familiar with Access than T-SQL so that's where I
go first. As I gain T-SQL (and SQL Server n general) experience I might
change my attitude, but for now I only put things on the BE if I can't
get it to work on the FE. So far that has only been queries that run
slow but I need to be able to edit. In this case a Passthrough doesn't
work as they can't be edited (AFAIK), but a linked View can be.

--
Bri

Nov 13 '05 #25

P: n/a
>>I do have TS access at one client and it makes life a lot easier.
With
there app, I am keeping the logic in the FE because they are
considering
a change in their server DB form SQL Server to whoknowswhatorwhy for an
other app. <<

I can understand why you've done it the way you have in this case.
Also, I am much more familiar with Access than T-SQL so that's where I
go first. As I gain T-SQL (and SQL Server n general) experience I might
change my attitude, but for now I only put things on the BE if I can't
get it to work on the FE. <<

I've done it a number of different ways, but I tend to gravitate to the
other side--a focus on the server side and stored procedures. This is
probably because I do a lot of work in ASP.NET and .NET tends to move
you to do everything with stored procedures.
In this case a Passthrough doesn't

work as they can't be edited (AFAIK), but a linked View can be. <<

If you have the rights, you should be able to run any statement with an
Access pass-through query that you do with SQL Query Analyzer. That
includes INSERTs or UPDATEs or even DROP TABLE. You can call a stored
procedure that inserts or updates records. You just can't get a result
set with a SELECT and then Expect to edit it like a linked table or
view because nothing is going to create INSERTs, UPDATE's and DELETEs
behind the scenes for you.

I understand, though, that this is the advantage of using links. They
do the dirty work so you don't have to. However, I think that you're
also right in that the more comfortable you get with working in T-SQL,
the more you see the benefits of moving at least some of your logic
inside the database.

Bill

Nov 13 '05 #26

P: n/a
I think there is some advantage to using a compiled stored procedure on the
server over a pass-through regarding optimization.
"Bri" <no*@here.com> wrote in message
news:KtT6e.979945$Xk.517568@pd7tw3no...
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,EmployeeID,SignoutDate,Comment) " & _
"SELECT InventoryID,'1','26-Jan-2005','' FROM InventoryAvailable " & _
"WHERE Inventory.ItemID = 7 " & _
"SELECT @@RowCount as MyRowCount"

Set qd = db.QueryDefs("qryTempPassthroughAction")
qd.SQL = stSQL
qd.ReturnsRecords = True
Set rs = qd.OpenRecordset
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.

Nov 13 '05 #27

P: n/a
Get your client to give you access to a machine via PCAnywhere or to the
server via VPN. With VPN you can connect to the SQL database on their server
and use tools such as Enterprise Manager on your machine, while modifying
the remote db. Works very well.

"Bri" <no*@here.com> wrote in message
news:QFc7e.1003022$6l.390214@pd7tw2no...
bi********@netscape.net wrote:
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.


If I were onsite as an employee, I would think the same as you. However,
I'm an independant consultant and I have no access to the Server unless I
make an appointment with the Server Admin, drive across town, make the
change, drive back.... I would rather make the change to the FE, e-mail
the changed object to the SA and have him Import it into the base copy of
the FE and then have the users get a new copy of it. Also, IMNSHO, having
all the logic in one place makes it easier to troubleshoot a problem than
to have it in two places.

--
Bri

Nov 13 '05 #28

P: n/a
Hey, at least you have a nemesis. If he spends that much time trying to get
under your skin you must represent something that offends him. Maybe his
mother was named "Larry."

Neil
"Larry Linson" <bo*****@localhost.not> wrote in message
news:Q6K6e.11314$H_5.3906@trnddc01...
"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.

Nov 13 '05 #29

P: n/a
> 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).


You still need to install MSDE on the machine before it can be used. It's on
the Access 2000 disk.
Nov 13 '05 #30

P: n/a
Bri
Neil wrote:
I think there is some advantage to using a compiled stored procedure
on the server over a pass-through regarding optimization.
Yes, there will be some time for the passthrough to be
compiled/optimized. This is similar to the difference in an Access
stored query and a SQL string in VBA. With today's hardware it is almost
instant. My point was that it was possible (and in some cases
desireable) to keep all of the business logic in the frontend and still
be able to take advantage of serverside processing.

Neil wrote: Get your client to give you access to a machine via PCAnywhere or to the
server via VPN. With VPN you can connect to the SQL database on their server
and use tools such as Enterprise Manager on your machine, while modifying
the remote db. Works very well.


I have one client who has given me Terminal Server access to the server.
This works great, but I still feel more compfortable having the logic in
one place and leave the server DB itself to be just the data repository.
I'm not saying that this is the prefered solution for everyone, its just
what I feel more comfortable with.

--
Bri

Nov 13 '05 #31

P: n/a
> Hetrogeneous database support.

You're saying Jet doesn't have heterogeneous database support and SQL Server
does??
Nov 13 '05 #32

P: n/a
Neil wrote:
Hetrogeneous database support.

You're saying Jet doesn't have heterogeneous database support and SQL Server
does??


I should prefix that with "transparent". In SQL Server you can use views
to get data from other databases and servers. To the front end it all
looks like one data source.

Sure you can connect to different databases using the front end but
that's a lot of code to execute each time you re-attach tables compared
to one data source.

--
[Oo=w=oO]

Nov 13 '05 #33

P: n/a
I have a procedure I created called ptRRun as follows:

Public Function ptRRun(strSQL As String, Optional strReturnField As String =
"ReturnVal") _
As Variant

Dim qd As QueryDef
Dim rs As Recordset

Set qd = CurrentDb.CreateQueryDef("", gconPTStartSQL)
qd.Connect = GetConnect()
qd.ReturnsRecords = True
qd.SQL = strSQL
qd.ODBCTimeout = 600
Set rs = qd.OpenRecordset

If rs.RecordCount > 0 Then
ptRRun = rs(strReturnField)
End If

End Function

GetConnect() referenced there simply returns the ODBC connect string.

gconPTStartSQL is some dummy SQL I use as a placeholder to create the query.
(Why not just use the passed SQL when the query is created, you might ask?
Good question. I don't know why. I think when I created this I was running
into some sort of glitch with certain kinds of SQL in creating the query.
Don't remember now -- it was 3 years ago.)

There's also error trapping and so forth which I removed here for
simplicity.

So, to call it, I just pass the SQL and either the name of the return field
to use, or just use the default "ReturnVal." To get the total balance due
for all invoices for a customer, for example, I use:

BalDue = ptRRun("SELECT Sum(INVBALNCE) AS ReturnVal FROM Invoices WHERE
(INVCUST=" & CustID & ")")

So the function allows me to harness the power of the back end with the
simplicity of using dynamic SQL.

Neil
"Tom van Stiphout" <no*************@cox.net> wrote in message
news:eq********************************@4ax.com...
On Tue, 12 Apr 2005 17:22:50 GMT, Bri <no*@here.com> wrote:

Hi Bri,
That's not quite what I had in mind. Here is a simplified example:
create procedure test1(@intOutput Output)
as
@intOutput = 5

Now call this sproc using DAO, and get the return value. Your
recordset approach will not work.
As Larry said, there is a way, but I don't know what it is.

-Tom.
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,EmployeeID,SignoutDate,Comment) " & _
"SELECT InventoryID,'1','26-Jan-2005','' FROM InventoryAvailable " & _
"WHERE Inventory.ItemID = 7 " & _
"SELECT @@RowCount as MyRowCount"

Set qd = db.QueryDefs("qryTempPassthroughAction")
qd.SQL = stSQL
qd.ReturnsRecords = True
Set rs = qd.OpenRecordset
MsgBox "Signed out " & rs!MyRowCount & " Items"

Nov 13 '05 #34

P: n/a
When I think "heterogeneous" I think of different database "types", rather
than just different databases. Heterogeneous database support is one of the
key features of Access -- being able to use data from SQL Server, Jet,
dBase, etc.

I agree that it's nice being able to join two tables from different dbs
together in a view. The same could be said about being able to use views in
general, and not having to write a query in the front end each time. But
it's still limited to SQL Server-based data.
"Trevor Best" <no****@besty.org.uk> wrote in message
news:42**********************@news.zen.co.uk...
Neil wrote:
Hetrogeneous database support.

You're saying Jet doesn't have heterogeneous database support and SQL
Server does??


I should prefix that with "transparent". In SQL Server you can use views
to get data from other databases and servers. To the front end it all
looks like one data source.

Sure you can connect to different databases using the front end but that's
a lot of code to execute each time you re-attach tables compared to one
data source.

--
[Oo=w=oO]

Nov 13 '05 #35

P: n/a
Neil wrote:
When I think "heterogeneous" I think of different database "types", rather
than just different databases. Heterogeneous database support is one of the
key features of Access -- being able to use data from SQL Server, Jet,
dBase, etc.

I agree that it's nice being able to join two tables from different dbs
together in a view. The same could be said about being able to use views in
general, and not having to write a query in the front end each time. But
it's still limited to SQL Server-based data.


Microsoft call it heterogeneous and we are talking about back ends.
Steve's question was the advantage of a SQL Server back end over an
Access one, the front end is still Access (see subject) and whatever you
can do in an Access front end applies to both scenarios, if you link
another table in your Access back end it's not accessible in the front end.
--
[Oo=w=oO]

Nov 13 '05 #36

P: n/a
No need to get defensive here. I'm just saying that the word "heterogeneous"
means, literally "different kinds" or "different types," and it's usually
used when referring to data of two different types (like SQL and Access),
rather than two of the same type, but in different files (like two SQL dbs).
That being said, it's not that big of a deal, except to be clear when using
the word that you're referring to one and not the other. If the user wants
to merge SQL and Access data in a query, that has to be done in the Access
front end, and can't be done in SQL, which has no support for heterogeneous
queries of *different types* of data.
"Trevor Best" <no****@besty.org.uk> wrote in message
news:42***********************@news.zen.co.uk...
Neil wrote:
When I think "heterogeneous" I think of different database "types",
rather than just different databases. Heterogeneous database support is
one of the key features of Access -- being able to use data from SQL
Server, Jet, dBase, etc.

I agree that it's nice being able to join two tables from different dbs
together in a view. The same could be said about being able to use views
in general, and not having to write a query in the front end each time.
But it's still limited to SQL Server-based data.


Microsoft call it heterogeneous and we are talking about back ends.
Steve's question was the advantage of a SQL Server back end over an Access
one, the front end is still Access (see subject) and whatever you can do
in an Access front end applies to both scenarios, if you link another
table in your Access back end it's not accessible in the front end.
--
[Oo=w=oO]

Nov 13 '05 #37

P: n/a
Neil wrote:
No need to get defensive here. I'm just saying that the word "heterogeneous"
means, literally "different kinds" or "different types," and it's usually
used when referring to data of two different types (like SQL and Access),
rather than two of the same type, but in different files (like two SQL dbs).
That being said, it's not that big of a deal, except to be clear when using
the word that you're referring to one and not the other. If the user wants
to merge SQL and Access data in a query, that has to be done in the Access
front end, and can't be done in SQL, which has no support for heterogeneous
queries of *different types* of data.


DTS?

--
[Oo=w=oO]

Nov 13 '05 #38

P: n/a
On Thu, 12 May 2005 07:31:34 GMT, "Neil" <no****@nospam.net> wrote:
No need to get defensive here. I'm just saying that the word "heterogeneous"
means, literally "different kinds" or "different types," and it's usually
used when referring to data of two different types (like SQL and Access),
rather than two of the same type, but in different files (like two SQL dbs).
That being said, it's not that big of a deal, except to be clear when using
the word that you're referring to one and not the other. If the user wants
to merge SQL and Access data in a query, that has to be done in the Access
front end, and can't be done in SQL, which has no support for heterogeneous
queries of *different types* of data.


Actually, SQL Server does have this ability, with one significant
limitation...

In SQL Server, there are 2 ways to query external data - one is to create a
Linked Server which is a misnomer because "server" just means ODBC data
source. There's another way that involves creating an expression in-line, but
I can't remember what that's called.

The limiation I was talking about is that the SQL Server, not the client,
needs access to the data source, and the connection string or DSN to the data
source must be described relative to the server's environment. Often that's a
big limitation, but often it's not.
Nov 13 '05 #39

P: n/a
Steve,
There's another way that involves creating an expression in-line, but

I can't remember what that's called. <<

I think that you mean OPENQUERY

Bill E.
Hollywood, FL

Nov 13 '05 #40

P: n/a
Steve Jorgensen wrote:
The limiation I was talking about is that the SQL Server, not the client,
needs access to the data source, and the connection string or DSN to the data
source must be described relative to the server's environment. Often that's a
big limitation, but often it's not.


Depends how you look at it, you may have database servers behind
firewalls and the one SQL Server you can access from your side you can
see selective bits of data from other databases you wouldn't
neccessarily have access to. That can be a good thing.

--
[Oo=w=oO]

Nov 13 '05 #41

P: n/a
I've only used DTS to import external non-SQL data. I wasn't aware that it
could be used to link non-SQL data and preserve it in that format (as Access
does with non-Jet data).

"Trevor Best" <no****@besty.org.uk> wrote in message
news:42**********************@news.zen.co.uk...
Neil wrote:
No need to get defensive here. I'm just saying that the word
"heterogeneous" means, literally "different kinds" or "different types,"
and it's usually used when referring to data of two different types (like
SQL and Access), rather than two of the same type, but in different files
(like two SQL dbs). That being said, it's not that big of a deal, except
to be clear when using the word that you're referring to one and not the
other. If the user wants to merge SQL and Access data in a query, that
has to be done in the Access front end, and can't be done in SQL, which
has no support for heterogeneous queries of *different types* of data.


DTS?

--
[Oo=w=oO]

Nov 13 '05 #42

P: n/a
So, technically, one could write data to an Access file (say, for selecting
records on a user's machine) and have SQL use that Jet data when processing
a query -- if one sent it parameters for how to connect to the Jet file on
the user's machine -- instead of having Access do the heterogeneous
processing on the front end. Is that correct?

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:nd********************************@4ax.com...
On Thu, 12 May 2005 07:31:34 GMT, "Neil" <no****@nospam.net> wrote:
No need to get defensive here. I'm just saying that the word
"heterogeneous"
means, literally "different kinds" or "different types," and it's usually
used when referring to data of two different types (like SQL and Access),
rather than two of the same type, but in different files (like two SQL
dbs).
That being said, it's not that big of a deal, except to be clear when
using
the word that you're referring to one and not the other. If the user wants
to merge SQL and Access data in a query, that has to be done in the Access
front end, and can't be done in SQL, which has no support for
heterogeneous
queries of *different types* of data.


Actually, SQL Server does have this ability, with one significant
limitation...

In SQL Server, there are 2 ways to query external data - one is to create
a
Linked Server which is a misnomer because "server" just means ODBC data
source. There's another way that involves creating an expression in-line,
but
I can't remember what that's called.

The limiation I was talking about is that the SQL Server, not the client,
needs access to the data source, and the connection string or DSN to the
data
source must be described relative to the server's environment. Often
that's a
big limitation, but often it's not.

Nov 13 '05 #43

This discussion thread is closed

Replies have been disabled for this discussion.