Conneting to SQL server, Need advice

[Patrick]

Can anyone give me some advice on working with SQL databases as I am
completely new to this.
I have a front-end access db(2003) working with linked back-end access mdb
tables (produkt definition) and I need to get additional data from an SQL
database (current stock).

Currently we do this by periodically exporting in Excel format from SQL-db
and importing the Excel into mdb
I want to change this so the front-end gets the data from the SQL-db
automatically.

What I have got so far is that I can create linked tables from this SQL-db
(found this in http://www.mvps.org/access/tables/tbl0010.htm)

Found that by not specifying a unique qualifier I can make these tables
read-only.

The SQL-db is maintained by 1 application. (stock control cabinet)
The front-end access db can be opened by several users at same time.
I do not want to interfere with this stock control cabinet application so
there are some concerns using the above method.

1: When opening the front-end will these linked tables be directly loaded or
only when these tables are opened? in other words: should I create the linked
table only when I need it and delete the link when I'm done or can I Linke
the table and leave it even if I not using it.
2: Are these linked tables opened as snapshot or do they dynamically
update/refresh?
3: Will opening these tables interfere with the main application used to
contol the SQL-db in ways of server-load / response speed?
4: What's better/wiser?: Linking a complete table, or SQL-ling the server
with VBA code to pass trough only those records that that I need?
5: Any more tips?

 

[pietlinden]

Can anyone give me some advice on working with SQL databases as I am
completely new to this.
I have a front-end access db(2003) working with linked back-end access mdb
tables (produkt definition) and I need to get additional data from an SQL
database (current stock).

Currently we do this by periodically exporting in Excel format from SQL-db
and importing the Excel into mdb
I want to change this so the front-end gets the data from the SQL-db
automatically.

What I have got so far is that I can create linked tables from this SQL-db
(found this inhttp://www.mvps.org/access/tables/tbl0010.htm)

Found that by not specifying a unique qualifier I can make these tables
read-only.

The SQL-db is maintained by 1 application. (stock control cabinet)
The front-end access db can be opened by several users at same time.
I do not want to interfere with this stock control cabinet application so
there are some concerns using the above method.

1: When opening the front-end will these linked tables be directly loadedor
only when these tables are opened? in other words: should I create the linked
table only when I need it and delete the link when I'm done or can I Linke
the table and leave it even if I not using it.
2: Are these linked tables opened as snapshot or do they dynamically
update/refresh?
3: Will opening these tables interfere with the main application used to
contol the SQL-db in ways of server-load / response speed?
4: What's better/wiser?: Linking a complete table, or SQL-ling the server
with VBA code to pass trough only those records that that I need?
5: Any more tips?

1. No. The tables are not queried unless you try to access them
somehow - through a form or query... (basically by using something
that's going to implicitly or explicitly query the SQL Server table).

2. Not sure... if you wanted them to be read-only, you could create a
read-only view in SQL Server and then link to it instead of the table
directly.

3. depends what you're doing... I used to work in a place where I
needed to grab a small subset of data from a single SQL Server table
when my database opened, and I never had any problems.

What exactly are you trying to do in your Access app with the SQL
Server data? My guess is that the best answer will depend on exactly
what you want to do with it... Right now (to me at least), your
question is not sufficiently specific. It's like you're asking what
kind of car to buy. A 4-cylinder car is great if you need good gas
mileage and zero towing capacity. It's absolutely the wrong choice if
you need it to tow a trailer... so the best answer I can give right
now is: It depends!! More information please!

 

[Patrick]

Some more info...

Access Db part:
Is used to:
- Define cutting tools (CT) and their geometrical/technical properties.
- Define CT assemblies
- define BOM per part. (witch+how many CT assemblies are needed to produce
part.)

SQL Db part:
In our shop we have a cabinet application -on sql db- that manages the
logistics of fysical CT's like: "actual storing location", "actual stock",
"generate orders when min. stock is reached", "new prices", "regrind prices",
and more....

What I am trying to achieve:
In the Access FE I want to be able to generate a forecast on how many CT's
should be purshased when user selects a number of (different) parts.
For this I would need to be able to READ the actual stock amount.
To make a forecast on costs I would need to READ prices and do some
calculating.

I have this functionality build in right now, based on manually importing
actual stock and prices into a temptable by "transferspreadsheet"

I want to get rid off this manually importing stuff.
I do not want to interfer to much with the cabinet application.

 

[Patrick]

Don't understand what you mean with parameterized query.
Prices do not change much, but # in stock changes daily.
At hte moment I use
DoCmd.TransferSpreadsheet acLink .... (so not actually importing)
to get data (also manually) exported from the cabinet, linked into FE
I needed to get rid of those two manual actions and I HAVE this now working
by linked ODBC tables from the SQL server.

My main question is: Is the the preffered way to get data from an SQL db?
Are there other options? like using a "SELECT * FROM sqldb WHERE * = *"
statement to populate a recordset on-the-fly.