How to query or SP to insert/upate a table from another DB's table

A

Alpha

Hi, I'm kind of new at Access. I know how to do this in T-SQL script for SQL
server but can someone tell me how to do this in Access 2000?
1. I need to select Distince(VID), Mileage, L-Service(3 columns) from
another Access DB's table-A, A ,and insert it into my DB table-B, B, if the
VID doesn't already exist
2. I need to update B. L-Service = A. L-Service where A.VID = B.VID
3. I need to update B.Mileage = A.Mileage where A.VID = B.VID

I would like this in a Stored Procedure if Access has this so I can invoke
it from my C# program. Otherwise, storing it as query should work as well.
I only see graphic interface area in Access for creating query. How can I
get to an area where I can just type in query statements? I've looked at my
Access 2000 book(pretty thick one) but found no help with what I'm looking
for.

Many thanks in advance,
Alpha
 
M

MGFoster

Alpha said:
Hi, I'm kind of new at Access. I know how to do this in T-SQL script for SQL
server but can someone tell me how to do this in Access 2000?
1. I need to select Distince(VID), Mileage, L-Service(3 columns) from
another Access DB's table-A, A ,and insert it into my DB table-B, B, if the
VID doesn't already exist
2. I need to update B. L-Service = A. L-Service where A.VID = B.VID
3. I need to update B.Mileage = A.Mileage where A.VID = B.VID

I would like this in a Stored Procedure if Access has this so I can invoke
it from my C# program. Otherwise, storing it as query should work as well.
I only see graphic interface area in Access for creating query. How can I
get to an area where I can just type in query statements? I've looked at my
Access 2000 book(pretty thick one) but found no help with what I'm looking
for.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access doesn't have stored procedures - use the queries.

You'll have to link db B to db A (use File > Get External Data > Link
Tables to select & link the desired tables). In db B create the query
by clicking on the Queries tab (or menu item in the left-side menu bar)
in the Database Window & double-clicking on the "Create Query in Design
View" in the upper-left corner of the Queries window. When the graphic
query design window appears (called QBE grid) on menu bar click View >
SQL View & type in your SQL statement.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyi0XYechKqOuFEgEQJ9zQCeJT9z8Knw2O3bLwxGGkgQCZcIBgcAoKbc
OE8/kXlpVlgQebUyCdRMs6kP
=vCwK
-----END PGP SIGNATURE-----
 
P

peregenem

MGFoster said:
You'll have to link db B to db A
Click to expand...

Not true e.g. the OP could query the external data via ODBC:

INSERT INTO MyTable (key_col, data_col)
SELECT T1.key_col, T1.data_col
FROM [MS Access;DATABASE=C:\MyOtherDB.mdb;].MyTable AS T1;

Access doesn't have stored procedures - use the queries.
Click to expand...

That's a bit misleading here Jet 4.0 has a CREATE PROCEDURE syntax
which creates a 'parameter Query' i.e. the PARAMETERS declaration is
seen in the Access UI. From the OP's point of view, this may be of
little comfort because a Jet procedure can only execute one SQL DML at
a time, so their 3 steps would require two procs (one for the INSERT
INTO..SELECT..IF NOT EXISTS... and one for the UPDATE).
 
A

Alpha

Brilliant, that's exactly what I need. This is the query I have created in
Access (I found the SQL view after I post to the forum yesterday). I
imported a copy of the table I needed(Data) and just to try out the query. I
will add your code to my query and that should do it for me. Thanks a
million, Alpha

INSERT INTO VehDetail ( VName, LastOdometerDate, LastOdometerReading )
SELECT DISTINCT [Data].[d_RemoteName], Max([Data].[d_DateTime]) AS
MaxOfd_DateTime, Max([Data].[d_OdometerTenths]) AS MaxOfd_OdometerTenths
FROM Data
GROUP BY [Data].[d_RemoteName]
ORDER BY [Data].[d_RemoteName];


[email protected] said:
You'll have to link db B to db A
Click to expand...

Not true e.g. the OP could query the external data via ODBC:

INSERT INTO MyTable (key_col, data_col)
SELECT T1.key_col, T1.data_col
FROM [MS Access;DATABASE=C:\MyOtherDB.mdb;].MyTable AS T1;

Access doesn't have stored procedures - use the queries.
Click to expand...

That's a bit misleading here Jet 4.0 has a CREATE PROCEDURE syntax
which creates a 'parameter Query' i.e. the PARAMETERS declaration is
seen in the Access UI. From the OP's point of view, this may be of
little comfort because a Jet procedure can only execute one SQL DML at
a time, so their 3 steps would require two procs (one for the INSERT
INTO..SELECT..IF NOT EXISTS... and one for the UPDATE).
Click to expand...
 
A

Alpha

Thank you very much for your reply and help.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access doesn't have stored procedures - use the queries.

You'll have to link db B to db A (use File > Get External Data > Link
Tables to select & link the desired tables). In db B create the query
by clicking on the Queries tab (or menu item in the left-side menu bar)
in the Database Window & double-clicking on the "Create Query in Design
View" in the upper-left corner of the Queries window. When the graphic
query design window appears (called QBE grid) on menu bar click View >
SQL View & type in your SQL statement.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyi0XYechKqOuFEgEQJ9zQCeJT9z8Knw2O3bLwxGGkgQCZcIBgcAoKbc
OE8/kXlpVlgQebUyCdRMs6kP
=vCwK
-----END PGP SIGNATURE-----
Click to expand...
 
Top