473,322 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

time card example question Access ADP SQL Server 2005

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
1 3815
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
by: JC | last post by:
I am very new to programming and learning on my own. Why do I keep getting duplicate values using this code? I want to shuffle a deck of 52 cards. The logic seems right to me. Randomize For...
8
by: sam1967 | last post by:
I know how to build forms with ASP and process user input. Now i want to take credit card payments via a form. i will then download the details and process them via our credit card machine. do...
6
by: Simon Wigzell | last post by:
My client wants to have credit card information fields on his forms for his website visitors to be able to buy his wervices by credit card. The credit card info - Brand, number and expiry date will...
10
by: dries | last post by:
A friend of mine has a problem with his credit card validation routine and it is probably a simple thing to solve but I cannot find it. It has to do with the expiry dates. What happens is that as...
2
by: jimfortune | last post by:
I'm looking for recommendations for automated time card systems that work well with Access. I only need the system to be able to identify employees and start and stop times in order to integrate...
7
by: Shimon Sim | last post by:
I have a custom composite control I have following property
9
by: Bob Achgill | last post by:
I would like to use the timestamp on files to manage the currency of support files for my VB windows application. In this case I would only put the timestamp of the file in the management database...
5
by: Peggy | last post by:
Hello, I have a web application running on IIS, What I wanna do is to obtain server's ethernet card's mac address in order to use this address for licensing issues I have developed my web...
6
by: Rob | last post by:
VB.net 2005 Windows app... makes calls to 2005 SQL server... sometimes I get a Timeout error, other times I do not... What is the best way to handle timeout errors ? 1. How to increase the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.