473,700 Members | 2,510 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL SERVER User Interface through ACCESS forms

Hello,

I would like to know if it is possible to create a form in Access2000,
which would function like a calendar for 8 operating rooms in
hospital, showing which hours are those closed for a specific date.
For this intersection I would like to be appeared the name of the
doctor, the name of the patient and the kind of operation.
Furthemore I would like this intersection to be marked in the calendar
with a colour, showing that this room is closed for that time.
The data for this form are going to be extracted from a table stored
in SQL Server 2000.
Generally the whole application is stored in SQL Server( stored
procedures, tables, diagrams etc.)

1)How this can be done? Is this going to be through VB or not? Can I
make a template in Excel spreadsheet put it in a form
and apply code on it?
I would like also to make forms(in Access) for user-entry data that
will use stored procedures of SQL Server with pushing the OK button.
The stored procedures would take as arguments the user-entry data from
the
forms.
I thought to make a pass-through query which would use a VB function
and would take as arguments the user's values
and after would pass these values in the stored procedures.

2)Can this be done through the pass-through query, that is Sql
Server "understand s" VB or better Access pass-through query process
the VB commands before be connected with the Sql Server?
What do you think of this as an idea?
My problem is that I really don't know how to combine VB code with
Transact Sql code.
Can you give an example or any ideas where I can find the relative
information?

Thanks you in advance.
Jul 20 '05 #1
3 16879
You have two choices using access 200x:

access data project (.adp)
only supports sql server
views, sprocs, diagrams, tables are all accessible in the project as if
your were working in sql server
uses sql server security
forms, reports, etc.. are designed specifically to be used with sql
server, include sprocs with parameters

access jet database (.mdb)
supports sql server through odbc
views and tables must be linked using odbc
sprocs can be linked using pass-through queries, but unless you know
vba/ado/dao, you won't be able
to run sprocs with parameters
uses jet security unless you don't save the password with the linked
table, then it uses both.

In a nut shell, since you don't know vba and if you want to get up and
running right away I would suggest the adp format. Using jet with sql
server, especially in the case of sprocs with parameters, requires a working
knowledge of not only vba, but ado or dao also. I would add that there are
a lot of very well-seasoned jet programmers out there however, and plenty of
examples of code if you decide on jet (comp.databases .ms-access). lots of
smart people there when it comes to jet

<po*******@yaho o.com> wrote in message
news:42******** *************** **@posting.goog le.com...
Hello,

I would like to know if it is possible to create a form in Access2000,
which would function like a calendar for 8 operating rooms in
hospital, showing which hours are those closed for a specific date.
For this intersection I would like to be appeared the name of the
doctor, the name of the patient and the kind of operation.
Furthemore I would like this intersection to be marked in the calendar
with a colour, showing that this room is closed for that time.
The data for this form are going to be extracted from a table stored
in SQL Server 2000.
Generally the whole application is stored in SQL Server( stored
procedures, tables, diagrams etc.)

1)How this can be done? Is this going to be through VB or not? Can I
make a template in Excel spreadsheet put it in a form
and apply code on it?
I would like also to make forms(in Access) for user-entry data that
will use stored procedures of SQL Server with pushing the OK button.
The stored procedures would take as arguments the user-entry data from
the
forms.
I thought to make a pass-through query which would use a VB function
and would take as arguments the user's values
and after would pass these values in the stored procedures.

2)Can this be done through the pass-through query, that is Sql
Server "understand s" VB or better Access pass-through query process
the VB commands before be connected with the Sql Server?
What do you think of this as an idea?
My problem is that I really don't know how to combine VB code with
Transact Sql code.
Can you give an example or any ideas where I can find the relative
information?

Thanks you in advance.

Jul 20 '05 #2

<po*******@yaho o.com> wrote in message
news:42******** *************** **@posting.goog le.com...
Hello,

I would like to know if it is possible to create a form in Access2000,
which would function like a calendar for 8 operating rooms in
hospital, showing which hours are those closed for a specific date.
For this intersection I would like to be appeared the name of the
doctor, the name of the patient and the kind of operation.
Furthemore I would like this intersection to be marked in the calendar
with a colour, showing that this room is closed for that time.
The data for this form are going to be extracted from a table stored
in SQL Server 2000.
Generally the whole application is stored in SQL Server( stored
procedures, tables, diagrams etc.)


Possible but it will require a lot of coding.
If you are not the boss GET EVERYTHING IN WRITING.
Once you get it running somebody is going to ask you for a simple method to
move operations and extend the times.
I had a similar job once in the early 80's with MSFT Basic and a DOS based
system.
The people I worked for sold the job, got a good bit of cash, then told me
about it.
I was real close when the hospital pulled the plug.

There are some visual scheduling packages out there.
Jul 20 '05 #3
po*******@yahoo .com wrote in message news:<42******* *************** ***@posting.goo gle.com>...
Hello,

I would like to know if it is possible to create a form in Access2000,
which would function like a calendar for 8 operating rooms in
hospital, showing which hours are those closed for a specific date.
For this intersection I would like to be appeared the name of the
doctor, the name of the patient and the kind of operation.
Furthemore I would like this intersection to be marked in the calendar
with a colour, showing that this room is closed for that time.
The data for this form are going to be extracted from a table stored
in SQL Server 2000.
Generally the whole application is stored in SQL Server( stored
procedures, tables, diagrams etc.)

1)How this can be done? Is this going to be through VB or not? Can I
make a template in Excel spreadsheet put it in a form
and apply code on it?
It'd probably be possible to get something to work from excel.
Be aware that this is one of those apps where even someone with a lot
of excel or access experience could struggle.
If you knew someone was a pro, you'd have asked em...
I would like also to make forms(in Access) for user-entry data that
will use stored procedures of SQL Server with pushing the OK button.
The stored procedures would take as arguments the user-entry data from
the
forms.
Lyle's the guy for ADP stuff...
I'd write this in vb.net rather than access if I was doing it.
By the time you mess about with all the adp stuff you may as well be
working in vb anyhow.
YMMV
I thought to make a pass-through query which would use a VB function
and would take as arguments the user's values
and after would pass these values in the stored procedures.

2)Can this be done through the pass-through query, that is Sql
Server "understand s" VB or better Access pass-through query process
the VB commands before be connected with the Sql Server?
What do you think of this as an idea?
Many devs using access on sql server just use linked tables and odbc.
No stored procedures.
You could well find there is little difference in overhead that way.
Depends on your nuber of users, how dynamic the data is, security
requirements etc etc.
My problem is that I really don't know how to combine VB code with
Transact Sql code.
Can you give an example or any ideas where I can find the relative
information?
You can make an ado connection and execute T-SQL as a string or invoke
stored procedures.
Look up connection and command in help for more info....
When using access I lean more towards just connecting forms up to the
database, this'd probably mean unbound fields and hence writing code
to populate them.

On the off chance you literally meant do this in VB...
Here's a vb.net example of code to run a stored proc.
The ado bits are pretty similar to doing this from access.

Dim sconn As String = Conn_String()
Dim conn As SqlConnection = New SqlConnection(s conn)
conn.Open()
Dim cmdWrite_22 As SqlCommand = New SqlCommand("Cap acity_22_Months ",
conn)
Try
With cmdWrite_22
.CommandType = CommandType.Sto redProcedure
.Parameters.Add ("@Bucket_Rules _Id",
CStr(Me.cboBkts .SelectedValue) )
i = .ExecuteNonQuer y
End With
Catch ex As SqlException
MsgBox("Error: " & CStr(ex.Errors( 0).Number) & " " &
ex.Errors(0).Me ssage)
End Try
conn.Close()

Thanks you in advance.


All these are my opinions, feel free to read the faq on advice as to
how valuable these may or not be.
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
6772
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:
4
2307
by: Bon | last post by:
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to migrate the backend database from MS Access 2000 to MS SQL Server 2000. However, I want to keep the MS Access 2000 interface. Would it be possible?
8
3113
by: Brendan Reynolds | last post by:
I'm trying to integrate SQL Server Reporting Services reports into an ASP.NET app (SRS 2000, ASP.NET 1.1). I know how to do this using direct URL addressing, but this exposes in the query string parameters that should not be exposed. Each user is associated with a school, and should see only that school's data. When the user logs in, I retrieve the SchoolID associated with that user, and that SchoolID is used as a parameter in all stored...
11
6101
by: Bob | last post by:
I am in the process of upgrading an Access database to SQL Server (and climbing that learning curve!). The wizard happily upgraded all the tables and I can link to them OK using ODBC. The application controls allocation of revisions to aircraft maintenance manuals for an airline type operation. In the application there is a form loaded at start-up allowing the user/s to select the records that they are currently interested in from 4...
5
2913
by: B1ackwater | last post by:
We've fooled around with Access a bit, but only using the single-user store-bought version. It seems to be a good database - versatile and infinitely programmable - and can apparently be used as a front end to SQL server if we ever needed to go that route. But - is there a client/server version of Access ? Looking on the CDW site there is a bewildering variety of packages and licences and such, but we can't figure out just which do...
2
6958
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of attending interviews. If you own a company best way to judge if the candidate is worth of it. http://www.questpond.com/InterviewRatingSheet.zip
2
4721
by: Wimpie van Lingen | last post by:
Hey I have some more questions with regards to Remoting in .NET 2. I'm using TCP with the Binary formatter. My solution consists of 4 projects: - Class Library containing the server classes which Inherits MarshalByRefObject (ok, at this stage it only contains one class... but its gonna grow) - Class Library containing common classes and interfaces that will be shared between all projects. This include interfaces for the server...
0
1652
by: Bill E. | last post by:
I will be creating an application using MS Access as a client to SQL Server 2005. Each user will have the client installed on his/her machine. Some users will be attached to the local network where the SQL Server resides. Others will access the network via VPN. I was thinking of using an MDW workgroup file located on a network server to control access to various user interface elements (i.e., forms, reports) using defined groups. In...
0
8649
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9219
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8975
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8931
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7819
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5904
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4660
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3097
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2032
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.