| Groups | Blog | Home |
sql server (alternate) : SQL SERVER User Interface through ACCESS forms
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 "understands" 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?
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 [quoted text, click to view] <polytimi8@yahoo.com> wrote in message news:42a43c0e.0306261148.7d257e8@posting.google.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 "understands" 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.
[quoted text, click to view] <polytimi8@yahoo.com> wrote in message news:42a43c0e.0306261148.7d257e8@posting.google.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.
[quoted text, click to view]
polytimi8@yahoo.com wrote in message news:<42a43c0e.0306261148.7d257e8@posting.google.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... [quoted text, click to view] > 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 [quoted text, click to view] > 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 "understands" 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. [quoted text, click to view] > 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(sconn) conn.Open() Dim cmdWrite_22 As SqlCommand = New SqlCommand("Capacity_22_Months", conn) Try With cmdWrite_22 .CommandType = CommandType.StoredProcedure .Parameters.Add("@Bucket_Rules_Id", CStr(Me.cboBkts.SelectedValue)) i = .ExecuteNonQuery End With Catch ex As SqlException MsgBox("Error: " & CStr(ex.Errors(0).Number) & " " & ex.Errors(0).Message) End Try conn.Close() [quoted text, click to view] > > Thanks you in advance. All these are my opinions, feel free to read the faq on advice as to
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |