Link Access Front end to adp?

[jazminecat23]

Hi all -

I have to create a front end for an adp that links to a sql server db. When I create my access db, I try to do the usual File--> Get External Data --> Link tables, navigate to my adp, and try to link to the table I want. Access says I can only do that between access dbs.

So, how can I create a front end that I can deploy to my end users that will link to the adp on the backend? This adp is constantly updated from the sql server, so it has realtime data in it that I need to get at and display in forms and reports. I need to be able to write to it as well. Do I need to make my forms and reports in the adp? Will that look the same to my endusers as an access db?

thanks!

 

[JoeAtWork]

Two choices:

Make another ADP that links to the same SQL server. It will look about 95% the same as a regular MDB. There are a few differences, the main thing being that any forms you want to be editable must include the table's primary key in the RecordSource.

Second choice is to make ODBC links to the SQL Server tables in an MDB. There is practically no difference to the look of your forms with this method (although I think the mandatory primary key still applies). The link to the SQL server tends to be slower than an ADP, however.

 

[lameid]

I have to create a front end for an adp that links to a sql server db.

Unless you miswrote, it sounds like you do not have a clear understanding...

An ADP IS a special Access FRONTEND that uses a SQL SERVER database as a backend. It does not support links to other database formats like an MD(B/E) does.

If you want the data an ADP sees in a traditional Access database, you need to link to the SQL Server database tables using ODBC. Or you could continue to develop in the ADP to add the necessary functionality. Please note that there is a forum dedicated to this... forum958

If you are going to use the MDB, you should learn about SQL Pass Through queries so you can use them to better your performance where needed. Alos note that you can link an SQL View as a table in Access (views are updatable like queries are in Access).

 

[jazminecat23]

ok, this is making better sense. Since our IT department won't allow me to connect to the sql db directly with an access db, they are insisting I use ADP. So i need to create my forms and reports in the ADP then, right? Ok. it's a little different than Access, what with the queries being different, but I think I can muddle through - esp with the help of that other forum. Thanks for clarifying this for me, because yeah, I was so not getting it.

 

[JoeAtWork]

Your IT department is kind of funny. Someone can do just as much mayhem to the SQL Server with an ADP as they can with ODBC links. They might be able to do it more efficiently with the ADP however

 

[lameid]

I agree with JoeAtWork, your IT department is kind of funny...

They MAY be under the impression that you can only use trusted connections in Acccess with an ADP which is not true.

They may also be under the impression that it is much easier to change a server location in an ADP than an MDB. This is probably true as each object in an MDB would have to be changed but it is easy to fix programatically.

They may also be thinking that in an MDB people will link directly to the table which can cause a ton of overhead. Although I have not tested this scenario but seems likely.

They may also by crazy loons... Or perhaps there is something I am missing. All I am saying is that if an MDB makes more sense because of multiple kinds of data sources, it may be worth a conversation.

Otherwise simple sql is not much different in SQL server than Jet... The query designer is more or less kicked on its side and things look a little different otherwise. Don't get me wrong, there are significant differences between the two. Until you start using functions, you wouldn't see much difference even looking at the SQL.

 

[jazminecat23]

Crazy loons. I work in government. It's obvious now, huh? Mostly I think they do this because it means we have to go through them. It's a control issue. They're annoyed that people have gone around them and built Access dbs to get things done, because we can't get things done going through IT. Because why? Because it's government. Fun, huh? Thanks for all your help, guys - I'm sure I'll be back if I get stuck.