DAO Code after conversion to ADP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mrrcomp - 04 Apr 2006 23:46 GMT

Hi

I upsized an Access 2000 application that is already connected to SQL
Server so that It would convert the Queries down to the SQL also. However I
now am not able to run code that is written in DAO. DAO 3.6 is referenced
however the following still doesn't run

Dim db As DAO.Database, tbl As DAO.TableDef
Set db = CurrentDb
Set tbl = db.TableDefs("TblMembers")

It Can't set the DB

Suggestions??

Regards

Meir

Reply to this Message

Brendan Reynolds - 05 Apr 2006 11:50 GMT

CurrentDb returns a reference to the current JET database. In an ADP, there
is no current JET database, so CurrentDb returns Nothing. You'll need to use
ADO in your ADP. The nearest equivalent to CurrentDb in ADO is
CurrentProject.Connection, but there is not a direct one-to-one
correspondence. In the example you posted, what you need to do depends very
much on what comes next - what you do with the TableDef once you have a
reference to it?

Signature

Brendan Reynolds
Access MVP

> Hi
>
[quoted text clipped - 15 lines]
>
> Meir

Reply to this Message

Bill Mosca - 10 Apr 2006 20:59 GMT

I'll let Brendan be your ADP guide, but I'd like to offer an alternative.

IMHO, ADPs are no better as interfaces to an SQL Server database than a
regular MDB or MDE front end linked to tables in an SQL Server back end.

Because you can use Jet with linked tables, your DAO code will work fine and
there won't be other pitfalls that occur with ADPs.

If you do not have SQL Enterprise client installed, you can use an ADP for
building tables, stored procedures and such and then do your front end
developing in the MDB.

Signature

Bill Mosca

> Hi
>
[quoted text clipped - 14 lines]
>
> Meir

Reply to this Message

Brendan Reynolds - 10 Apr 2006 22:01 GMT

> I'll let Brendan be your ADP guide

<snip>
Thanks, but I believe I'll pass! :-) I'm an MDB/DAO person too. If Meir does
decide to stick with the ADP, there are people who know much more than I do
about ADPs hanging out at the microsoft.public.access.adp.sqlserver
newsgroup. Those using Microsoft's web-based interface to the newsgroups can
access that newsgroup at ...
http://support.microsoft.com/newsgroups/newsReader.aspx?dg=microsoft.public.acce
ss.adp.sqlserver

Signature

Brendan Reynolds
Access MVP

Reply to this Message

mrrcomp - 12 Apr 2006 12:11 GMT

Hi Brendan and Bill

Thanks for the inputs. My comments:

1) I am using the ADP because the Wizard was able to convert the Queries up
onto the SQL Sever as views and stored procedures.

2) Brendan thanks for the tip as to the CurrentProject.Connection will give
that a try and see how I can continue with it..

3) Will move over to the SQL Newsgroup for more indepth help!

Thanks to you both..
Meir

> > I'll let Brendan be your ADP guide
> <snip>
[quoted text clipped - 4 lines]
> access that newsgroup at ...
> http://support.microsoft.com/newsgroups/newsReader.aspx?dg=microsoft.public.acce
ss.adp.sqlserver

Reply to this Message