Newbie question about using Access wi SQL Server

G

gemel

I work with SQL server but I have been asked recently to provide some
Access forms that will provide a connection to Access tables, SQL
tables, and sometimes both. I want to use SQLL to do this job and I
have set about the job as follows.

1. I have made external connections to the SQL server database as
linked tables. The tables are now available to Access.

2. I create a connection object to the current project

3. I create a recordset based on SQL queries.


Now I can populate the recordset using the SQL


Is this the recommened method of making the data available to Access.

Also, I'm not very sure about how I bind the controls to the data so
that when I move the pointer through the recordset then I will get the
appropriate effect on the controls. Can I bind the controls to the
recordset as a whole?


Thanks in advance

John L
 
A

Albert D.Kallal

The normal approach is to setup a linked table. At that point, you can thus
just use ms-access as you always did.

The above of course assumes you have a good design, and don't do brain dead
things like open a form attached to a table without some type of "where"
clause to restrict record loaded. Hence, I NEVER EVER just lazy open a form
to a table without first asking the user what information they want to view.
(I of course do this for my JET based applications, and for sql server
ones). Here is a example of what I mean by "asking" the user what they want
before loading the form:

http://www.members.shaw.ca/AlbertKallal/Search/index.html
1. I have made external connections to the SQL server database as
linked tables. The tables are now available to Access.

2. I create a connection object to the current project
Click to expand...

I don't see why you have to do the above? Once you got those linked tables,
then just build your forms and applcation as you always did...
3. I create a recordset based on SQL queries.


Now I can populate the recordset using the SQL


Is this the recommened method of making the data available to Access.
Click to expand...

Not really. Once you got those links setup, then your application should
function as before. Of course, you must be more sensitive to bottlenecks,
and of course now that the data is not on your computer, but on a server
somewhere, then you most certainly need to LIMIT the number of records you
load, and work with at a given time (however, as mentioned, any good
application will do this anyway).

And, sure, the connection object can be of use to run stored procedures, and
do some data processing stuff. However, you don't need to build a recorset
and use the connection object if you use linked tables and forms with bound
controls.
 
G

gemel

Thanks for the very valuable information. But if I want to use tables
from my Access database and also tables from my SQL server in the same
query then would that be a situation where my earlier method could be
used?

Regards

John L
 
A

Albert D.Kallal

gemel said:
Thanks for the very valuable information. But if I want to use tables
from my Access database and also tables from my SQL server in the same
query then would that be a situation where my earlier method could be
used?
Click to expand...

Sure. If had to with data from two sources, then I would pull the sql server
data to your local machine, and into a temp table (and a temp mdb file due
to bloat issues). I would then do the queries and join of the data. You do
NOT want to join data from different sources in ms-access, as it tends to
perform VERY poorly. With small amounts data (say, only a few thousand
records), then you might get acceptable performance with multiple
datasouces.

You can test and play with this and see how it runs for you. However, you
best pull the data you need from sql server, and then join/work with this
data and your local data. Better yet would be to put all the data on sql
server, and have it do the crunching, and send only the RESULTS of the work
to your pc...
 
Top