Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    28

    Unanswered: MS SQL hiding behind Access

    Hi guys

    We have a helpdesk application which is based on an MS SQL database, and which runs with a rather large and complicated C# based interface. We don't have the code for this, so we can't customise it. Long story short, what we want is to create an interface in Access (or similar) which accesses the same database as the helpdesk suite, and allows reading AND writing, and some rather complex operations (which SHOULD be fairly simple to do in SQL).

    Unfortunately, I have been given this project, and I know about as much about Access and SQL as I do about Ghengis Khan's fashion sense.

    I will follow with more information as required, but I'm going to need a lot of help with this one. First things first, is Access the way to do this? Is it going to be easier to create a new Access DB and synchronise it with the SQL, instead of both applications using the same database?

    The only way I can get Access to interrogate the SQL database is to create a Data Access Page - is this the correct starting point? The only problem is that this seems to only offer HTML, and is far from being a friendly interface, at least not to me.

    I know that I currently have no grounding in physics and I'm trying to build a space station, but any advice that you could give me would be much appreciated.

    Thanks guys!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Access is the tool if you want it done fast and you do not have a firm grounding in another programming language.

    Read about Access Data Projects (.adp files).
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Agree with Thrasy thingy me bob.
    You don't need to use adp but it is rather well suited for use against a SQL Server BE.
    You don't need to use DAP unless you specifically want an html based interface. If you do use this you will find the forum resources at your disposal drastically reduced because they don't get used that much.

    You do not want to create an Access db and synchronise with SQL Server. Either link to the server (simple) or use ADO code to create a disconnected interface (a bit complex if you are new to this but a more ideal set up).

    Is this a third party product? If so then check your agreement with the supplier. You might be invalidating your agreement and they may, for example, be able to withdraw support. Also be aware that there may be some (a lot??) of business knowledge embedded in the C# FE which you will have to infer. Similarly, there are probably sprocs that enforce some validations etc for inserts and updates - you could probably do with looking at these and using them where you can.

    I must say - I would be nervous about writing my own FE to an application I haven't built\ don't have access to the source code. Can you not use the C# app for inputting and limit yourself to procuding a bespoke reporting front end instead?

    There is an Access forum for questions of a more access orientated nature. I think you need to prepare yourself for a very steep learning curve if you aren't familiar with Mr Kahn's clothing predelictions.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2005
    Posts
    28
    Steep learning curves are generally the way things are done around here. My biggest problem is having to forget everything I know about Lotus Notes in order to start learning about REAL databases!!

    The data access project seemed to be what I wanted - I now have an SQL database which is apparently pretending to be an Access database, so assumedly I can just forget all about SQL and work with Access forms and VB code as though this was an Access DB, is that correct?

    Also, just to stop me destroying the world, is it easy to create a copy of the MS SQL DB and work with that instead, just until I know what I'm doing. If I break this database there are several peole that will be after my blood!!

    In case you're wondering, the reason for this project is that the C# interface provided is fine for support staff, and works well, but unfortunately has all the customizability of a brick, and doesn't support the sales/invoicing staff very well. So we want to keep the C# interface for the support staff, but make a different one for the sales staff. Is there going to be any problem with both parties accessing the data at the same time? It is VERY unlikely that we will ever overlap on editing entities, due to the (quite) seperate nature of the two sides to the system.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by fishkake
    The data access project seemed to be what I wanted - I now have an SQL database which is apparently pretending to be an Access database, so assumedly I can just forget all about SQL and work with Access forms and VB code as though this was an Access DB, is that correct?
    Nope. I'm not too familiar with adpsbut they are really more the other way around as I understand them - they bring much of the SQL Server functionality into Access - Access becomes a sort of rudimentary SQL Server tool (as well as a UI designer).

    Quote Originally Posted by fishkake
    Also, just to stop me destroying the world, is it easy to create a copy of the MS SQL DB and work with that instead, just until I know what I'm doing. If I break this database there are several peole that will be after my blood!!
    Oh dear lord yes do that. At the minimum have a test database where you fine tune any of your work before using it on the production database. I would suggest that using this test database indicates that you have some idea of what you are doing rather than something to dump once you can write a few lines of code. I'm not sure if it is possible to destroy the world with SQL Server though that might be one of the new features in 2005.
    Gist is - you want to backup your database and then restore it (ideally on a different, dedicated server). I am afraid I use enterprise manager to create and restore backups - a bad habit I will probably be chastised for. Check out BACKUP in BoL and Google to avoid learning bad habits.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •