I having difficulty to make link with two databases.

First database   - abc.mdb fields   x1,x2, x3   - primary key x1
Second database - xyz.mdb field  x1, y2, y3 - x1 -> index (no duplicate)

I want to display x1, x2, x3, y2, y3 join field x1. Can someone help me with the full SQL command.

Thanks in advance.

SELECT     [abc].x1, [abc].x2, [abc].x3, [xyz].x1, [xyz].y2, [xyz]y3
FROM         [abc] INNER JOIN [xyz] ON [abc].x1 = [xyz].x1

Of course, if you use the query builder in Access or SQL, you can do it graphically, which is a lot easier.

Thanks JL. I have bigger problem than this.

I have three databases in three different locations.

1. Report.mdb   -> tablename (report) -> fieldsName (extension, dialed, COS)
2. 020827-o.mdb -> tablename (outgoing) -> fieldsName (extension, dialed, type)
3. site.mdb -> tablename (extensions) -> fieldsName (extension, COS)

What I want to do is, insert all data from 020827-0.mdb->outgoing-> fields (dialed, extension) to report.mdb-> report->fields (dialed, extension) where outgoing!type = 'L'  and

Update report.mdb->report->COS where site.mdb->extensions![extension] = report.mdb->report![extension]  (needs to make inner join)

I tried with the below commands for the first part but doesn’t work

open dbs 020827-0.mdb  

lsSQL = "INSERT INTO report IN '" & App.Path & "reportsreport.mdb' ([extension], [dialed]) select [outgoing].[extension], [outgoing].[dialed] from [OUTGOING] where outgoing![type] = 'L'"

DBs.Execute lsSQL
got "syntax error in INSERT INTO COMMAND".

I am using vb5 and access97.  In the help menu shows insert into command as follows: INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])] SELECT [source.]field1[, field2[, ...] From tableexpression

Can someone please check where I have made a mistake? and please send me a fresh idea along with code for the queries.

Its very important for me and i will be really appreciated for any help.

Thanks in advance.

Long day, so can't think too hard.

There's info in the MSDN library: "INSERT INTO", under the "external databases" section

IT shows uses the "IN" command in the select part, not the insert. (IE, have the INSERT INTO table be in the current database, SELECT table be the remote db).

However it does suggest using a Linked table over a SQL command with external databases.






[Edited by JLRodgers on 14-09-2002 at 03:47 PM GMT]

thanks for your prompt reply. i will try again as you said.

Also, if you can take sometime to look at it and send me codes later.

Thanks a lot.

Just noticed, your code had:
     reportsreport.mdb
your database names had it as: report.mdb.

If the two names are correct, that could be a problem.

I created the databases (report, and 020827-o) and 1 table each (report with 2 columns, 020827-o with 3).

In the 020827-o database (I just stuck the query there) the following query did work (it's how accessXP formatted it and everything ):

INSERT INTO Report ( extension, dialed ) IN "Report.mdb"
SELECT extension AS extension, dialed AS dialed
FROM Outgoing
WHERE Type='L';


[Edited by JLRodgers on 14-09-2002 at 04:12 PM GMT]

Thanks JL. That report name there was a typing mistake during my posting.

My insert into commands working fine. There was problem with IN clause.

Can you please tell me about the second part:

what i want to do is: update report->COS field as per extensions->COS where report![extension] = extensions![extension]

I can make a query but i want to update field. How can i do that?

Never tried to update fields with multiple database tables, but the gerenal update syntax is:

UPDATE tablename SET fieldname=value WHERE conditions

I tried in a various ways but failed to use update command linked with multiple databases. Also,  no help from MSDN. However, is there any way that I can copy all data from a recordset to a table?

Set RST = dbs.openrecordset(“SELECT Classes.Cos, Classes.Name, Extensions.Extension, Extensions.FirstName FROM Classes INNER JOIN Extensions ON Classes.Cos = Extensions.Cos”, dbopensnapshot)

Lets say, RST is having 10,000 data and I want to generate a report.

Do while not rst.eof
   Insert into a table…….
   Rst.movenext
loop

If I use above loop command then, it will take a long time to insert all data to a table. What is the easiest way?

Please help!  

If the tables in the SQL statement are in the same database, just modifying the query should work. (note that the destination table must have the same field names).

INSERT INTO [tablenamedest]
SELECT Classes.Cos, Classes.Name, Extensions.Extension, Extensions.FirstName FROM Classes INNER JOIN Extensions ON Classes.Cos = Extensions.Cos


By using a recordset, there isn't that I know of, but then I've never tried. I just use the query that created the recordset as above.

Actually Access program is having great facility.

My program creates a MDB file everyday. so, i have to generate a report for a certain period. That means, may be, have to use 365 mdb files if my program wants to generate a yearly report. Additionally, each database linked with three other access databases.

Glad, i did it. My program is working fine.

Thanks a lot JL.

If someone wants to know how, then let me know. i will send you full codes along with sample mdb file.

Shahidmojid   8)        

i'd like see the code with the database sample.
Thank you.   

Hi Dimitris,

Click the link below and download the project.

http://www.geocities.com/shahidmojid/vbpractice_DbLink.htm

Thanks

Shahid