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
42  5651 
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
"Terry Kreft" <te*********@mp s.co.uk> wrote in
news:y5******** ************@ka roo.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
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******** ********@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
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.c om... 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
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******@pcdata sheet.com www.pcdatasheet.com
"Larry Linson" <bo*****@localh ost.not> wrote in message
news:UCX6e.1444 6$ox3.5223@trnd dc03... 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******** ********@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
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(@intOutpu t 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,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.ReturnsReco rds = True
Set rs = qd.OpenRecordse t
MsgBox "Signed out " & rs!MyRowCount & " Items"
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******** ********@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
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_afterupdat e 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]
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.c om... 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(@intOutpu t 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,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.ReturnsReco rds = True
Set rs = qd.OpenRecordse t
MsgBox "Signed out " & rs!MyRowCount & " Items"
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********@bwa y.net.invalid> wrote in message
news:Xn******** *************** **********@24.1 68.128.78... "Terry Kreft" <te*********@mp s.co.uk> wrote in
news:y5******** ************@ka roo.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 This thread has been closed and replies have been disabled. Please start a new discussion. |
