ADP Files....

[gbaughma]

So, we have this huge MDB that links to access backends (several of them), which I would like to upscale and convert to a SQL backend.

So I ran the upscale wizard, it created all of my SQL tables on SQL, and created an ADP file.

But the ADP file has no VB Source code... it's blank. What did I do wrong?

TIA!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

Meh... The source code was there, but the modules weren't. I re-created those.

Now on to the next task... this original MDB had linked tables to a backend database, but also LOCAL tables... is there a way to do that? The ADP file doesn't want to seem to let me create a local table.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[Axworthy]

ADP removes the Jet database therefore no local tables or queries can be created.

 

[danvlas]

No, adp files can't have local tables.


Daniel Vlas
Systems Consultant

 

[gbaughma]

Well, that answers that... I won't be able to convert this to a SQL backend (easily, anyway).

(Sigh)

Is it quitting time yet?

Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[danvlas]

Quitting time? NEVER! )

What's the use of the local tables? User defined lookups?
1: You may have them in local mdb's, placed on all workstations in exactly the same location.
You can get their data through ADO connections (but you can't set relationships or write SQL statements on both SQL and Access

2 (my favourite): Add a few columns to them to identify each set of records, then unify all the instances of a local table into SQL table.


Daniel Vlas
Systems Consultant

 

[dhookom]

I recommend not creating an ADP. You can link the SQL and Access tables into an MDB file. I believe this is more in line with Microsoft's future plans for Access.

Duane
Hook'D on Access
MS Access MVP

 

[gbaughma]

dhookom:

I would agree with you, except for the following minor problems.

1) It's access XP.
2) The access app that is being written is going to be installed on a bunch of machines.
3) I don't want to have to set up DSN on said machines.
4) Access XP doesn't seem to have a native way to link tables to SQL (at least none that I've found yet).



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[dhookom]

I have been linking SQL Server tables for many years using XP and earlier versions. I rarely create DSNs since I have many users in many locations.

Typically I use a little code modified from using DSN-Less Connections from Doug Steele that modifies the connection string of the linked tables. This code can also modify the connections of pass-through queries.

Duane
Hook'D on Access
MS Access MVP

 

[MasterRacker]

I'll second Duane's method. Works great and you can get a lot more flexibility.

Ran across this method years ago in Access 2 when we had problems with links need ing to be "refreshed". Linking them in a VBA startup function every time solved it.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[JoeAtWork]

Yes, I also use DSN-less linked tables. I never liked ADP's, everything became to "rigid", and local tables have their uses.

 

[danvlas]

Pro ADP, because of the MDB security flaws:

The 'Connect' field in the MSysObjects table from the Access database may contain sensitive information. It's rather easy to link that table to another database and the info is revealed.

You need to give user write permissions on the tables to use the 'usual Access coding'. This is unacceptable in many systems, as it opens the database to severe SQL injection attacks.

If you login as a different user, who has no permission on the linked tables, you'll probably get an error message at 're-linking time'. This doesn't happen in ADP - you just open the file and see what you're allowed to.

The queries will probably run slower (except the 'pass-through' ones), since they are run outside the database server. SQL Server cannot save execution plans for such queries.

If a table structure is changed on the server, you must re-link it, as its definition is not automatically updated in the mdb file.

The only thing I miss from Access is the cross-tab query, which is great in Access but extremely poor in SQL Server.

Dan


Daniel Vlas
Systems Consultant