Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    New Zealand
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Connecting an ADP to MDB without SQL Server (2002)

    Is it possible to use the currentproject.connection with the access.jet data provider to connect an adp file to an mdb without needing to go through SQL Server and use a linked server?
    I am trying to reuse all the code in an ADP file for either an MDB or SQL backend without having to maintain two files. I have already written many routines that alter SQL between JET and ANSI so that is not an issue.
    Does anyone have any experience or tips with this. Is it possible?

    TIA
    Simon Lampen

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Connecting an ADP to MDB without SQL Server (2002)

    In general you can use ADO for code related things and move back and forth with only occasional problems, but the hitch is likely to be queries, forms and reports, as they are rather different beasts in an ADP compared to what you get with a MDB. In particular, queries are quite different beasts from stored procedures and views that are used with an ADP. Why not use the Desktop engine rather than trying to use an MDB front-end? (There are a number of things that are more flexible or powerful with an MDB format, so perhaps I've answered my own question.)
    Wendell

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    New Zealand
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connecting an ADP to MDB without SQL Server (2002)

    Hi Wendell,
    Thanks for your prompt reply. I am currently using the MSDE but am also looking for a 'Lite' backend option with having to maintain 2 front ends. The issues that you raised are exactly what I am trying to understand. Sure I could code a whole app in VB or similar doing all the updating with ADO connections etc. What I was hoping to acheive was the reuse of forms and reports with a few small embedded code options for an MDB or SQL database.
    My first thought was simply to use the currentproject.connection and use a Jet dataprovider and see if forms and reports would work with that. My next thought would be to bind each form to a jet ado connection but the idea would be to have access handle as much of the binding and updating.
    The differences between stored procedures and queries I am not so concerned about as the crux of it is really just the form and report binding that I am interested in as all the rest I can handle with generic ADO connections.

    Thanks
    Simon

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Connecting an ADP to MDB without SQL Server (2002)

    If you're talking about swapping between a SQL Server/Desktop and an MDB *back end*, you can't. ADPs are strictly SQL Server front ends. The only way to make them play with MDBs is through links in the SQL Server back end.

    If you're talking about sharing front end code with either an ADP or an MDB front end, there are other problems ... like the fact that forms bound to an ADO recordset are editable in an ADP but not in an MDB. So you would either have to use unbound forms (and forego continuous forms and subforms) in both or have two separate sets of forms. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Connecting an ADP to MDB without SQL Server (2002)

    As Charlotte points out, you are stuck with having two files if you want to have an ADP, but you could share some common code between them. In addition, the power of binding reports and forms would be lost so everything would need to be done in code. But the real crux of the issue in my mind is the vast difference between how queries work with a Jet backend and stored procedures work in a SQL back-end. Yes, you can use Pass-Thru queries to run stored procedures in SQL, but then you have to figure out how to do a comparable thing with a Jet backend. Bottom Line: If you want a common front-end, use an MDB format and use ODBC links to SQL tables.
    Wendell

  6. #6
    New Lounger
    Join Date
    Jan 2001
    Location
    New Zealand
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connecting an ADP to MDB without SQL Server (2002)

    Thanks for your responses. It looks like I will have to make some decisions about which road to go down.

    Cheers
    Simon Lampen

Posting Permissions

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