Axport Query from Access to another MDB

[tobynegus]

Hi
Is it possible to export a query result to another Access database or pull an Access query result into a Access database?

any help much aprreciated

Toby7

 

[tobynegus]

I mean EXPORT not Axport :-(

 

[bubba100]

If you can link db A to db B then you could query the information as if the data was local.

 

[tobynegus]

got it, link table in B to A, use append query in A to fill the linked table in B

Thanks

 

[boblarson]

Actually, you don't need to link them in order to do this. You can use a query that directly inserts data without linking the tables.

http://msdn.microsoft.com/en-us/library/bb208861.aspx

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[sxschech]

You could use the "IN" statement in your append query (or Select, Update, etc).
This example shows appending from the table in the other database to another table in that same "other" database from the currently open database.

You could change the IN to refer to a different "other" database, or you could remove the IN for the part that you want to remain in the currently open database.

INSERT INTO tblCollegeSort ( CampusSort, CampusName ) IN 'C:\TEMP\MYOTHERDatabase.mdb'
SELECT tblCollegeSort_Bak.CampusSort, tblCollegeSort_Bak.CampusName
FROM tblCollegeSort_Bak IN 'C:\TEMP\MYOTHERDatabase.mdb'
WHERE (((tblCollegeSort_Bak.CampusSort)=1));

The nice thing about this method is there is no coding or linking required.

Using this technique, you can do the folowing or combination:

Be in Database1 and run a query in Database2 into Database1
Be in Database1 and run a query in Database2 into Database2
Be in Database1 and run a query in Database2 into Database3
Be in Database1 and run a query in Database1 into Database2
Be in Database1 and run a query in Database1 into Database3

 

[tobynegus]

i get an Sytax Error in the INSERT INTO statement, can anyone help?

INSERT INTO tbl_TurnAround1 ( TestCode, CodeDesc, CntCode, SellPrice, who, [where], ReqEntRsltAvgTA, ReqEntRsltMaxTA, ReqEntRsltMinTA, RsltEntAuthAvgTA, RsltEntAuthMaxTA, RsltEntAuthTA, ReqEntAuthAvgTA, ReqEntAuthMaxTA, RsltEntAuthMinTA, DataDate, DataSource )

IN F:\mick nov\from c\PathlogyAgregate.mdb

SELECT qryBMI_Turnaround.[Test Code], qryBMI_Turnaround.Desc, Count(qryBMI_Turnaround.[Test Code]) AS [CountOfTest Code], qryBMI_Turnaround.Sell, qryBMI_Turnaround.Clinician, qryBMI_Turnaround.[Source(Locn)], Format(Avg([ReqEntRslt]/60),'Fixed') AS ReqEntRsltAvgTA, Format(Max([ReqEntRslt]/60),'Fixed') AS ReqEntRsltMaxTA, Format(Min([ReqEntRslt]/60),'Fixed') AS ReqEntRsltMinTA, Format(Avg([RsltEntAuth]/60),'Fixed') AS RsltEntAuthAvgTA, Format(Max([RsltEntAuth]/60),'Fixed') AS RsltEntAuthMaxTA, Format(Min([RsltEntAuth]/60),'Fixed') AS RsltEntAuthTA, Format(Avg([ReqEntAuth]/60),'Fixed') AS ReqEntAuthAvgTA, Format(Max([ReqEntAuth]/60),'Fixed') AS ReqEntAuthMaxTA, Format(Min([RsltEntAuth]/60),'Fixed') AS RsltEntAu
thMinTA, 01/11/2009, 'BMI' AS DataSource FROM qryBMI_Turnaround GROUP BY qryBMI_Turnaround.[Test Code], qryBMI_Turnaround.Desc, qryBMI_Turnaround.Sell, qryBMI_Turnaround.Clinician, qryBMI_Turnaround.[Source(Locn)], 01/11/2009 , 'BMI' HAVING (((qryBMI_Turnaround.Sell)>0));

 

[tobynegus]

sorted the SYntax
get 'can't find tbl_TurnAround1 (this is the table i am trying to put data in

INSERT INTO tbl_TurnAround1 ( TestCode, CodeDesc, CntCode, SellPrice, who, [where], ReqEntRsltAvgTA, ReqEntRsltMaxTA, ReqEntRsltMinTA, RsltEntAuthAvgTA, RsltEntAuthMaxTA, RsltEntAuthTA, ReqEntAuthAvgTA, ReqEntAuthMaxTA, RsltEntAuthMinTA, DataDate, DataSource )

IN 'F:\mick nov\from c\PathlogyAgregate.mdb' SELECT qryBMI_Turnaround.[Test Code], qryBMI_Turnaround.Desc, Count(qryBMI_Turnaround.[Test Code]) AS [CountOfTest Code], qryBMI_Turnaround.Sell, qryBMI_Turnaround.Clinician, qryBMI_Turnaround.[Source(Locn)], Format(Avg([ReqEntRslt]/60),'Fixed') AS ReqEntRsltAvgTA, Format(Max([ReqEntRslt]/60),'Fixed') AS ReqEntRsltMaxTA, Format(Min([ReqEntRslt]/60),'Fixed') AS ReqEntRsltMinTA, Format(Avg([RsltEntAuth]/60),'Fixed') AS RsltEntAuthAvgTA, Format(Max([RsltEntAuth]/60),'Fixed') AS RsltEntAuthMaxTA, Format(Min([RsltEntAuth]/60),'Fixed') AS RsltEntAuthTA, Format(Avg([ReqEntAuth]/60),'Fixed') AS ReqEntAuthAvgTA, Format(Max([ReqEntAuth]/60),'Fixed') AS ReqEntAuthMaxTA, Format(Min([RsltEntAuth]/60),'Fixed') AS RsltEntAuthMin

 

[sxschech]

Did you try a select on the table to see if it shows up?

SELECT *
FROM tbl_TurnAround1
IN 'F:\mick nov\from c\PathlogyAgregate.mdb';

If "F" is a network drive, you might also consider UNC Example:
\\servername\mick nov\from c\PathologyAgregate.mdb

instead of "F".