DAO Code after conversion to ADP

[mrrcomp]

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

 

[Brendan Reynolds]

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?

 

[Bill Mosca]

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.

 

[Brendan Reynolds]

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.access.adp.sqlserver

 

[mrrcomp]

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