Insert dataset to another database

  • Thread starter Thread starter JPM
  • Start date Start date
J

JPM

I'm posting this to this group also since it pertains to queries, primarily.


Hello,

Using VB6/ADO, I'm thinking I can create a recordset and insert it into
another table in a different Jet database, all within the execution of a
single query. But, I can't seem to get it to work, even in an experiment in
Access 2K.
My sql in Access 2K:

SELECT D.lorder as Ord, A.Lorder
FROM [;Database = C:\MyDocuments\Acc2K\Wrk.mdb].OrdersData as A INNER JOIN
[;Database = C:\Access\Work\Sales06.mdb].Detl1 as D On D.Lorder = a.lorder
WHERE ((D.fg)= 'MXX-NC' Or (D.fg)= 'MXX.NC');

If I've the Sales06 database file open and am working in it I shouldn't have
to specify that database path in the query, I'm theorizing, but regardless
whether I do or don't I get the error "No database specified in connection
string or IN string". I haven't set this up in VB yet, but I don't see why I
should bother if it won't work as an MsAccess query.
I know I mentione the idea of Inserting the result into a table of another
database; I can't even get a select across 2 Jet databases to work. BUT,
I've found examples from people like Doug Steele that apparently work.

'SELECT A.ExpenseID, A.JobID, B.Category
'FROM [;DATABASE=C:\LocumTE\LocumTEData.mdb].[Expense] AS A
' INNER JOIN [;DATABASE=C:\time&expense\time&expensedata.mdb].[Expense] AS
B
' ON A.ExpenseID = B.ExpenseID

Question is, what am I doing wrong with my version of this query?

Thanks for any input,

Jim
 
Link the two tables, then uses their local name?


If the tables are in the same external db, you can try to see if you can use
the FROM ... IN syntax:


.... FROM a INNER JOIN b ON a.f1=b.f1 IN "C:\somePath\dbname.mdb"


but that way, you can specify only one db.


Hoping it may help,
Vanderghast, Access MVP
 
Thanks, I do that all the time within Acces UI while generating reports,
etc. But I'm really after doing this programmatically, outside of MSAccess.

Michel Walsh said:
Link the two tables, then uses their local name?


If the tables are in the same external db, you can try to see if you can
use the FROM ... IN syntax:


... FROM a INNER JOIN b ON a.f1=b.f1 IN "C:\somePath\dbname.mdb"


but that way, you can specify only one db.


Hoping it may help,
Vanderghast, Access MVP



JPM said:
I'm posting this to this group also since it pertains to queries,
primarily.


Hello,

Using VB6/ADO, I'm thinking I can create a recordset and insert it into
another table in a different Jet database, all within the execution of a
single query. But, I can't seem to get it to work, even in an experiment
in
Access 2K.
My sql in Access 2K:

SELECT D.lorder as Ord, A.Lorder
FROM [;Database = C:\MyDocuments\Acc2K\Wrk.mdb].OrdersData as A INNER
JOIN
[;Database = C:\Access\Work\Sales06.mdb].Detl1 as D On D.Lorder =
a.lorder
WHERE ((D.fg)= 'MXX-NC' Or (D.fg)= 'MXX.NC');

If I've the Sales06 database file open and am working in it I shouldn't
have
to specify that database path in the query, I'm theorizing, but
regardless
whether I do or don't I get the error "No database specified in
connection
string or IN string". I haven't set this up in VB yet, but I don't see
why I
should bother if it won't work as an MsAccess query.
I know I mentione the idea of Inserting the result into a table of
another
database; I can't even get a select across 2 Jet databases to work. BUT,
I've found examples from people like Doug Steele that apparently work.

'SELECT A.ExpenseID, A.JobID, B.Category
'FROM [;DATABASE=C:\LocumTE\LocumTEData.mdb].[Expense] AS A
' INNER JOIN [;DATABASE=C:\time&expense\time&expensedata.mdb].[Expense]
AS
B
' ON A.ExpenseID = B.ExpenseID

Question is, what am I doing wrong with my version of this query?

Thanks for any input,

Jim
Click to expand...
Click to expand...
 
Back
Top