Linking to SQL

[mission2java_78]

I was wondering why I cant link SQL tables to an mdb. Do I have
to create an .adp to do this? I always thought access allowed any
type of linkage but when I try to link the tables I only get dbaseIII,
dbaseVI, dbaseV, excel, outlook, exchange, and some various other ones...
but no linkage to SQL tables. The only way I can see the SQL tables is
by creating an adp...is this the only route to take?

If so how does the upsizing wizard handle it then? Does it create an adp as well?
What about all of the forms one had previously built for their access application...
if he / she wanted to move up to SQL server how are these forms now bound to SQL
tables if one cannot link these tables?

Jon

 

[RV]

Jon,

you'll have to use an ODBC connection to access your SQL Server tables.

>if he / she wanted to move up to SQL server how are these forms now bound to SQL tables if one cannot link these tables?<

Actually, they aren't bound as you can't bound them in anyway.

HTH,

RV

 

[mission2java_78]

Not bound? You cant use a table as the record source of a form?

 

[RV]

Not directly, no, not that I've ever heard of
Anyway, you can't base your forms DIRECTLY on a SQL Server query / table.
You can interface database data using ODBC between Access and SQL Server buy you can't use SQL Server to build your forms on DIRECTLY (really bound).

I don't know whether is possible INDIRECTLY using ODBC connections and a lot of VBA.
Anyway, I guess you would be dealing with a performance being zip, nada, nill.

RV

 

[mbailey]

Just link the tables using ODBC. You can then use them like linked Access tables. You can use them as a form's recordsource. Performance is generally as good as or better than jet.

Marty

 

[Pat Hartman]

I think we're having a semantic problem here. Once you link to an ODBC data source, you can use the linked table EXACTLY as if it were a native Access table.

Jon,
One of the choices in the link tables dialog should be ODBC. When you select ODBC, you should see a list of the DSN's that have been defined. Choose the one that points to the SQL Server database that you want to connect to or add a new connection if the correct one is not yet defined. You will then see all the tables and views contained within the db.

It is NEVER a good idea to bind forms directly to tables and it is even worse to bind forms directly to ODBC linked tables. Your form should be bound to a query that has a where clause that limits the recordset to 1 or at most a few related records. If you bind forms directly to SQL Server tables, you will probably be hearing from your local DBA about what a pig Access is and why are you causing such a large amount of network traffic. If you bind a form to a table, Access will be forced to pull EVERY row of the table across the network to your workstation. It will display the form as soon as any data is available so it won't look to you as if there is a problem. But, Access will keep pulling in the background until every record has been retrieved. That's why you need to use queries with very specific where clauses.

 

[RV]

Semantic problem

Pat,

thanks for your crystalclear input (as always).
I couldn't have made myself clearer (actually, I even didn't come close ).

RV

 

[mission2java_78]

HI Pat,

I'll try this out this weekend. This applies to an mdb right ? Also when we talk about setting the actual form to a query...are you talking about a dynamic sql string assigned to the recordsource of the form? Or a query coming from SQL Server?


Jon

 

[mbailey]

Jon,

If you use ODBC to link to the server (in a .mdb), simply query them using the Access Query Grid, as you would any table. You can use these queries as the form's recordset.

Marty

 

[mission2java_78]

Access query grid? So just create the query right through access? Is what you're saying...ok I guess that makes sense...so basically SQL will just be my backend and everything else including reports is Access based. Im just trying to get an idea with SQL server...Ive been using access for a good 2 years and am ready to make the move .

Jon

 

[Pat Hartman]

The fact that you are using SQL server (or any other ODBC data base) can be totally transparent if your table and column names conform to the RDBMS' standards and your forms are based on stored querydefs.

I frequently build my db's with Access and "convert" them by simply deleting the Access tables and linking to ODBC tables. I keep the Access tables around so I can work on the db at home by simply swapping my ODBC links for links to Access tables.

 

[mission2java_78]

ok...one last thing..most of your posts say "Use queries to build the recordsources of your forms....with the usage of WHERE clauses". My question is you dont really always need a where clause in a recordsource do you? I mean say I have a form of customers who can make orders. I open this form to add a new order to a customer..when this form opens I dont want to limit my recordset using WHERE..I simply want to add a new record...in this case is there a need for a WHERE clause in the query? I rarely ever see any usage in the WHERE clause when it comes to setting the recordsource of a form.

Jon

 

[Pat Hartman]

If the DataEntry property of the form is set to yes, you may not need a where clause. The only way to actually find out, is to have the DBA monitor the connection to see how much data is being transported.