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

time card example question Access ADP SQL Server 2005

P: n/a
ken
Hi,
Lets say we have a form that displays time card entries based on the
calendar control date on the form. So the user clicks on a date and the
form filters the table where the time card entries come from to get you
proper data.
In access I would just filter the query(rewrite its qrydef) and I'd get
my answer.

My question is, what is the proper way of doing this in ADP and SQL
Server 2005. I could filter the data from the view on the form, but
that would be filtering on the front end(a lot of unused data will be
going over the pipes from the server as a result).

I was thinking of using the ALTER view statement to change the T-SQL of
the view...so depending on user selection it would be SELECT * from
vwNAME WHERE Date = FilterDate
FilterDate would be the result of the calendar control in access.
I was told this is not a correct way to do this? If so why not? I was
going to make an sproc that would have a parameter @FilterDate and
would run the above T-SQL code to alter the query.

The other thing I was thinking is since sproc results can't be updated
I would have to create my own show/filter and updates. So have an sproc
filter my data, then have another sproc update it?

Oh and I don't just need to view filtered data, I need to update it as
well.

Any help appreciated. I'm a newbe to SQL Server...still learning.

THanks

Aug 15 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi Ken,

As long as you are using sql server 2005, you should consider stepping
up to VS2005 (VB.Net2005). Believe me, it is much easier to interface
with sql server through .Net than through Access. With all due respect
to Access (where I started my microsoft DB career) later versions of
Access are being focused more on power users than Developers. The
developer crowd is all moving on to the .Net platform. I have migrated
several ADPs to .Net because there were just too many issues between
Access and sql server. VS2005 was designed specifically for sql server
2005. Access functions most efficiently in the mdb arena - less
efficiently in the sql server arena.

As far as a solution to your current situation, the problem is that ADPs
are permanently/continuously connected to live data. This really limits
data manipulation. .Net uses disconnected datasets. The solution to
your issue in the ADP is to create a physical table on sql server to
collect the filtered data and then display it. One trick that I used to
do was to separate users from their data pulls by adding their UserID to
the table. That way they only manipulate their data - add/delete rows
based on their UserID. But if you have several users using the same
table, that becomes quite inefficient. That is why the real solution is
to use the disconnected datasets of .Net. You write a sql string with
the date values you need, you pull the data to a dataset in your .Net
app and view it in a datagridview. Note: datagridviews are way nicer
than an Access form because you can dock a datagridview to a form
(actually, better to dock to a panel on a form). When you stretch the
form, the datagridview stetches with the form. If you have 20 columns
in the datagridview but the .Net form initially only displays 10
columns, you can stretch the .Net form to view all 20 columns.

I love (loved) Access but the time has come to go out with the old and
in with the new - as far as interfacing with sql server goes.

hth

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 15 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.