from an MDB right-click LINK to an ADP

[dbahooker]

hey

i've got an MDB and an ADP.

I want to link to a SQL table.. I've done this 100 times before; it
just somehow doesn't work for this client.

I need to know how to fix this.

open a MDB.

Right-click, Link.. browse to an ADP.. choose the ADP and then link a
table.

i've done this 10000 times before in my life.

Why does it just NOT WORK here for this client?

 

[Vadim Rapp]

Hello (e-mail address removed),
You wrote in conference
microsoft.public.access.adp.sqlserver,microsoft.public.access on 7 Oct 2005
17:44:46 -0700:

d> hey

yo

d>Right-click, Link.. browse to an ADP.. choose the ADP and
d>then link a table. i've done this 10000 times before in my life.

I doubt it. You can't link a table from ADP to an MDB. Access returns error
message "links can only be created between microsoft access database
files" - and rightfully so, since ADP does not have any talbes in itself.
Link to ODBC-> sql server table.


Vadim

 

[Albert D.Kallal]

I don't recall ever being able to right click, and use link to grab a adp
table.

However, this kind of begs the question, why are you not using a adp here?
(after all, that is all you spend your time hooting about anyway).

Perhaps you are having a brain freeze.

You can use file->get external data..and browse to a adp.....

At that point, you can get a link to the sql tables....

If you don't want to use get->external data, then link to the sql server,
but you have to use the odbc option from the drop down list when you link...

 

[aaron.kempf]

vadim yes you can

i swear to god it works for me all the time; and i just found one
machine it's not working on.. it's just pissing me off.

either way. you SHOULD be able to do this.

and im about 90% sure i used to be able to do this; I will test it on
my dozen different machines and let you know the results

and yes.. vadim.. i know that ADP doesnt contain tables lol

 

[aaron.kempf]

and i sometimes use MDB for a simple report.. mainly shaping data;
using vba functions.. and then i rewrite them as SQL Udfs.

I won't actually distribute new apps in mdb though.. i mean-- come on--
all the garbage with linked tables and locking problems?

gag me with a spoon

 

[aaron.kempf]

BAZ

THANK YOU-- YOU JUST SAVED ME A TON OF TIME!! I've totally been
working in an Access 2000 environment a LOT recently

I just think that ADPs are much more reliable/scalable solutions than
MDB.. i've just been tired of MDB locking mysteries for the past 10
years.

I mean-- it's like.. Access MDB just doesn't have a decent debugger for
SQL; and from where i'm sitting-- i'm personally tired of running
around and setting up DSNs and linking tables.. I'm tired of all the
extra work to change a SQL passthrough query.

In ADP; you can just run ALTER VIEW sql

there are a LOT of things that I love about MDB. I just wish that more
people took ADP as seriously as I do.



-aaron

 

[aaron.kempf]

I totally agree about the recorset thing; sounds EXACTLY the way i
remembered it.

I personally haven't ever had to use the form_error event.. never
really had a reason to i guess..

i just do simple bound ADPs and I _LOVE_ them.

And I want to know why people are so anti-adp.

I am so friggin sick and tired of having to copy queries between
frontends.

I dont agree with your BS about ADO. ADO is much better than DAO.. for
uh.. ANYTHING and anything that tells you otherwise is stuck in 1996.

DAO was a 4-year object model and ADO has been like a 7-year object
model.

Anyone using DAO after the release of Office 2000 should be SHOT.
There are better ways to do things.

DAO addnew, update, edit, delete
THESE ARE ALL A WASTE OF TIME. Editing recordsets is for sissys that
dont know how to write SQL.

I just dont understand what in the heck you're talking about.. MDB/ODBC
outweigh ADP?? yeah.. maybe on the suck-meter

I mean-- what in the heck are you talking about.

MDB / ODBC makes things about 100 times more complex; and it's utterly
unusable in my book.

I have MDB/ODBC in my bag--- for DEVELOPMENT; not for production use.

I love Access-- i wouldn't be the 'Sr SQL Developer' that I am today if
it weren't for MDB. And I claim that there are hundreds, thousands,
millions of people that can use ADP in order to learn more about SQL
Server.

And I think that it's the best data entry platform in the world.

I mean--- aren't you TIRED of changing sql passthroughs? Aren't you
tired of half of your code building SQL statements in order to update
Sql passthroughs?

The parameters and sprocs concept is much more powerful in ADP than
anything available in MDB. Do you know how to bind a form to a sproc?


Where if you have a parameter in a form; you can bind that to a form
field??

Just like you can in MDB?

It's the most beautiful thing in the world kid.

aren't you TIRED of having these circa-1996 errors?

MDB are _SHARP_ for a lot of things. But Microsofts' refusal to fix
bugs makes them unusable.

I just think that it's horse shit when people talk smack about ADP
because I dont think that most of these people know WTF they're doing.

-Aaron

 

[Albert D.Kallal]

i'm personally tired of running
around and setting up DSNs and linking tables..

None of us do that...we all use DSN-less links, and have code to re-link if
needed....

You can find the code here to do this...

http://www.accessmvp.com/djsteele/DSNLessLinks.html

 

[Vadim Rapp]

Hello (e-mail address removed),
You wrote in conference
microsoft.public.access.adp.sqlserver,microsoft.public.access on 8 Oct 2005
18:53:46 -0700:

ak> vadim yes you can

ak> i swear to god it works for me all the time; and i just found one
ak> machine it's not working on.. it's just pissing me off.

ak> either way. you SHOULD be able to do this.

ak> and im about 90% sure i used to be able to do this; I will test it on
ak> my dozen different machines and let you know the results

with a screenshot please!

 

[aaron.kempf]

Albert

I dont have to deal with that code; I dont have to make the end users
wait 3 seconds when you open the app

i dont have screwed up locking errors

i dont have to worry about end users leaving their app open
i dont have to worry about copying a new query out to 100 different
desktops every week.

 

[aaron.kempf]

a) i dont ever allow people to change PK
b) i dont ever allow any deletes anywhere in any of my databases.

Listen dipshit; I've been at Microsoft most of the past 3 years.. I
have single-handedly developed terabyte datamart solutions; i have
written hundreds, if not thousands of cubes.

There is more to life that running around; waiting for Access to open..
i mean-- how many years do you think that you can ask your end-users to
wait 30 seconds to run a report?

30 seconds is too long and it's never happened to me once on SQL
Server.

it's just like this

when you work in MDB for so long.. you sit around and build queries
until they just plain CRAP OUT. how many times have you gotten some
fucked up error message in Access and you just have to say 'oh; that
query is too complex for MDB' so then you have to rewrite it to use a
temp table

THOSE DAYS ARE OVER AND SQL SERVER ISN'T THAT FLAKY

There are a lot of things that I love about MDB. but i wont ever use
them for end users-- it's just too much of a hassle to sit around and
deal with all that crap

-aaron

 

[aaron.kempf]

and I argue that 'ADO isn't Microsofts most redundant Data Access
Technology' it is DAO.

DAO is 10 years out of date; it isn't reliable... it isn't supported.

And I'm not sure I believe you when you say that DAO/Jet is going to be
updated.. but ADO won't.

All that Microsoft needs to do in the next version is to take ADP and
change it to ADO.net-- that is the way that things SHOULD work.

But then again, Microsoft has no understanding of the Access/VB market
and Redmond is run by a bunch of C++/C#/Excel idiots.

I mean-- I've been at Redmond for about 3 years altogether; I've never
met a single blue badge that knows their head from their ass and
they're all Excel dorks.

I just think that it's fucking funny that you guys think that MDB is
going to make some grand comeback-- but ADP is going to go away.

ADP will be here until the end of time. And when ADP goes away; I'm
not worried-- I've got jobs coming out of my ears-- for $70/hour--
that's my rate.. writing MDB or ASP or SQL or MDX or whatever the heck
my customers want.

I haven't had a week of unemployment since 2001?

Do you know why?

Because I grew into a SQL Server developer-- because I could write
views and sprocs instead of crappy little MDB queries.. and again-- the
thing about ADP is that it lets you write views and sprocs with a
designer component-- it is an excellent tool for writing queries THAT
WORK. Instead of bumbling through and adding a field and having your
queries crap out.

There are a dozen reasons not to use MDB. I personally know that
performance is more important than anything; and I know for a fucking
fact i can outperform any MDB app you've got.

You want to have a race? You give me your little boner MDB app; i can
rewrite it in ADP in about an hour.. and my reports will run faster
than your stupid MDB (especially when you run your MDB across a
network-- i mean-- come on, are there really limits like that in the
MDB world still lol)

I mean it script-kiddie-- let's have a race; and i'll take your MDB
report that takes 60 seconds to run and I'll have ADP spit it out in 1
or 2 seconds.

Better yet-- I wont have to reboot my file server once a day; i dont
have locking problems-- i won't have to run around copying and pasting
queries (which for sure change more often than anything else in a
database)

having a central repository of queries is the best reason to use ADP.

Analysis Services-- OLAP-- is the 2nd best reason to use ADP. Then you
can please the excel dorks at your company through a couple of
components that are included with office and sql server.. i mean--
there are better technologies out there than sitting around waiting 30
seconds for a report to run in a MDB.

i mean-- things like indexes?? real indexes?

welcome to the 21st century kid; stop playing with crap mdb

-Aaron

 

[Robert Morley]

I don't know if it applies when linking a front-end to SQL Server, but if
you're using an MDB back-end, you can reduce the re-linking time
significantly by opening an unrelated copy of the database...DAO seems to
cache it, and your linking goes much faster. I know it sounds stupid, but
just add this to the beginning of your re-linking code and you'll see...

Dim myDbVar As DAO.Database
Set myDbVar = DBEngine.Workspaces(0).OpenDatabase("backend.mdb", False,
False)

Don't forget to "Set myDbVar = Nothing" once you're done.

Also, you can check to see if the existing link is the same as the link you
would've been creating, and skip it if it is. While we've since moved on to
an ADP/SQL Server setup, back when we were on a front-end/back-end MDB
setup, linking took a fraction of a second in most cases.

But yeah, other methods have their advantages too.

As to copying a query to 100 different desktops...if you have to update that
many (or anything more than about 2 or 3), I think you might want to look
into an automated update procedure of some kind, regardless of what you're
using as a front/back end!

Remember, this isn't a competition...we aren't Mac vs. PC users. <grin>
It's about learning new things, and finding tips & tricks to get the job
done.



Rob

 

[dbahooker]

i just get off on storing queries in one place; having much smaller
front ends.. not having to copy tables and linked tables and all that
other garbage..

one connection-- it stays open so you have @@spid-- just look cookies
in the ecommerce world

i wish i could share with people some MDB stuff im doing.. i've seen a
LOT of mdb messes over the years-- I'm an Access guy.

I just have this system for automating the execution of queries; right
now it's on the MDB side-- i had it on the sql side back for phillips a
couple of months ago

i just want to know what similiar systems people have-- because i want
to learn-- i'm here to learn

i just wish that more people would take ADP seriously

and i know that we can convince ms to keep it around lol