Insert dataset to another database

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



0
JPM
4/11/2007 9:11:15 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
901 Views

Similar Articles

[PageSpeed] 4

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" <jpm@nospam.ever> wrote in message 
news:uAF%23x3HfHHA.4916@TK2MSFTNGP06.phx.gbl...
> 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
>
>
> 


0
Michel
4/12/2007 1:35:02 PM
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" <vanderghast@VirusAreFunnierThanSpam> wrote in message 
news:OxW9UgQfHHA.3648@TK2MSFTNGP05.phx.gbl...
> 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" <jpm@nospam.ever> wrote in message 
> news:uAF%23x3HfHHA.4916@TK2MSFTNGP06.phx.gbl...
>> 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
>>
>>
>>
>
> 


0
JPM
4/12/2007 3:02:49 PM
Reply:

Similar Artilces:

How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman Numerlas in my document? "ckj" <ckj@discussions.microsoft.com> wrote in message news:6CF0C580-BA6E-463D-B4FD-E6A6FDD9E814@microsoft.com... > New to Word 2007 would someone please explain to me how to insert Roman > Numerlas in my document? Just type them... I II III IV V VI VII VIII IX X XI etc etc. Type a number e.g. 21, select it and run the following macro Dim oRng As Range Set oRng = Selection.Range oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...

Lookup selective from another sheet
Assume I have one sheet as below. How can I create a new sheet and display only those entrys that are greater than a entered value. i.e main sheet abc 2 def 5 ghi 6 jkl 5 fgh 3 krk 4 on second sheet, if 4 is entered only entrys >4 are shown. i.e def 5 ghi 6 jkl 5 krk 4 Try something like this: C1=IF(VLOOKUP(A1,Sheet2!A$1:B$4,2,FALSE)>Sheet1!B1,VLOOKUP(Sheet2!A$1:B$4,2,FALSE),NA()) Your main sheet is A1-b6 and Sheet 2 is A1:B4 "Jim" <Jim Forrest@hotmail.com> wrote in message news:WBb4f.34203$U9.12412@fe3.news.blueyonder.co.uk... > Assume...

Migration options from E2k server to another E2k3 server
Hello, I have a doubt in which way is best suited to migrate data from a E2k server to another E2k3 server in the same domain. For more information, both are domain controllers, the E2k server is running under Windows 2k Server and the E2k3 is running under Windows 2k3 server enterprise edition. The last will replace th older E2k server. Thank you in advance, On Feb 12, 10:13 am, winpor <win...@discussions.microsoft.com> wrote: > Hello, I have a doubt in which way is best suited to migrate data from a E2k > server to another E2k3 server in the same domain. > For more inform...

Insert trigger
Looking for some advice on SQL 2005. I have a table that will usually be populated by an SSIS package. I want to set the "loaddate" column to the current time after a record is inserted. Should i do this via trigger or should i just build a step in the SSIS package to update the column after the file loads? If trigger is the way to go, what is the syntax to create the after insert trigger? Thanks in advance. You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date assignment without any need for coding. -...

inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex- "40:15" is there a way to make it more like this...40hrs,15mins -- Message posted from http://www.ExcelForum.com Use a custom format hh"hrs",mm"mins" -- Regards, Peo Sjoblom "-Brian-H- >" <<Brian-H-.110wgs@excelforum-nospam.com> wrote in message news:Brian-H-.110wgs@excelforum-nospam.com... > I have a cell in my time card that displays total weekly time -ex- > "40:15" is there a way to make it more like this...40hrs,15mins ? > > > ...

insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then save it was as a web page, but I want to keep the excel part interactive. Any ideas? ...

Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options" On making new-email with Attachment-File(s), ** File Menu | Insert | File >>> (Brows and select File to insert ) then we can see the button "Insert", and write side Drop Down Arrow lower-right side of Dialog Box; If it clicked, we can see three options as follows: ** Insert Insert as text Insert as Attachment I can not recognize/understand the difference between "Insert" and "Insert as Attachment" *** What is the difference between Insert and Insert As Attachment ? I would appreciate y...

Inserting Hyperlinks in a Protected Sheet
Hi I run Excel 2000 and I have a protected worksheet that I share wit users in my organisation. I want to allow the insertion of a hyperlin to a specific file type within a specified directory on our server. 3 Questions: 1.Protection on disables the insert hyperlink command. Can this b overcome with worksheet activate code? 2.Can I limit the types of files (preferably by requiring the file t meet a mask format eg "z-*.xls")? 3.Can I limit the directory that can be linked, by referring to pathname stored in a cell on the active sheet? Would appreciate your suggestions. Thanks S...

Insert with a where condition
Hi, sql 2005 I have an insert statement that is ignoring the where condition. That is, I want to insert records when they do not already exist in the destination table. INSERT INTO dbo.tblmnuGroupPerm ( gId ,mtfID ,... ) SELECT @gID ,mtfID ,... FROM dbo.locmnuTabFunction AS ltf WHERE ltf.mtfID NOT IN ( SELECT gp.mtfID FROM dbo.tblmnuGroupPerm AS gp WHERE gp.gId=@gID AND gp.Deleted=0 ) Any ideas or recommendations appreciated :-) Many thanks, Jonathan It's OK... <oops "redFace">I did not correctly se...

Exchange 2003 Database Size vs Free Space
I am running Exchange 2003 Standard Edition SP2 We currently have approximately 100 mailboxes priv1.edb is 14.4GB priv1.stm is 8.8GB I did, quite some time ago update the registry to increase the database size limit to 75GB. Event ID 1221 reports only 220mb of free space. I suspected it was misreporting the free space because my backups were running at the same time the maint (online defrag) was running. I changed my backup to run outside of the maint. window but it's still reporting the same amount of free space. Am I reading this wrong? It looks like I'm ou...

Insert
I want to overtype in a Publisher text box. I find I can only insert. The "insert" key doesn't do anything. ...

How Many People Have Personal SQL Databases & Which Software is Most Popular?
Hi, I am writing an application that converts data into SQL table format - rows and columns. I would like to know what software packages there are that people use to create and maintain SQL databases, and how many people use each - say within the United States. Thanks for any help, Charlie ...

how to insert data in a table
Hi Exprets; I am creating an access database in which I want to insert data in already created table. Kindly help. Regards, Vikky Vikky <love.excel@gmail.com> wrote in news:1194124711.012302.269990 @e34g2000pro.googlegroups.com: > Hi Exprets; > > I am creating an access database in which I want to insert data in > already created table. > > Kindly help. > > Regards, > > Vikky > Data from where? Do you want to import it from excel, from a text file, copy it from another table or type it in manually? -- Bob Quintal PA is y I've altere...

How do you insert page numbers larger than 1000?
I have my purchase orders set up as a Publisher document. When our organization upgraded from Publisher 2000 to Publisher 2002, the new version set parameters on the page numbers. This was one of those things that worked just fine in the previous version... Does anyone know how to turn it off or change it? Hi mregen (mregen@discussions.microsoft.com), in the newsgroups you posted: || I have my purchase orders set up as a Publisher document. When our || organization upgraded from Publisher 2000 to Publisher 2002, the new || version set parameters on the page numbers. This was one of those...

insert downloads into power point
i downloaded an application called "BioDigital Simulator" of an animated cleft lip/palate surgery and need it put into my power point presentation, but can't fiugre out how to do so.... very frustrating... What kind of file is this application? Is it a video? If so, what kind? MPEG? AVI? MOV? Or is it an EXE file? Something else? Which version of PPT are you using? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover...

utility for extracting email addresses from database
i will like to be able to extract all the users email address of my organization from the exchange database. Does anyone know of any utility out there that can extract that report to some sort of file. Mikeolo "mikeolo" <afaras@netzero.net> wrote: >i will like to be able to extract all the users email >address of my organization from the exchange database. >Does anyone know of any utility out there that can >extract that report to some sort of file. > >Mikeolo Depends on the version of Exchange. In 5.5 you can run Exchange Admin and do a directory ex...

Insert dataset to another database
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')...

sample database template
hi may i know if we are allowed to modify te sample northwind database and use it in our small usinesses Go ahead if it suits your needs. You may want to look on the Microsoft web site for other templates, or maybe search online, if Northwinds does not meet your needs. NOrthwinds is more of a sample database than a template, so you may find it is worthwhile to search for other options. Amin wrote: >hi may i know if we are allowed to modify te sample northwind database and >use it in our small usinesses -- Message posted via AccessMonster.com http://www.accessmonster....

Insert a blank row
Hi, I need some help to insert a blank row in a range where column A has a series of dates. There will be several of the same dates and I need to both sort the dates and then insert the blank row at the end of each sequence. In the blank row I need to total figures that will be in columns B through to G. Thanks, Jim S Hi Jim maybe an easier solution 1. Sort your data (use 'Data - Sort', e.g., column A ascending) 2. Use 'Data - Subtotals' This will insert a row after each date and calculate subtotals automatically for you HTH Frank anon wrote: > Hi, > I need some...

DPM Database Size
The DPM database on my primary DPM server is 30GB. The tbl_TE_TaskTrail table is 23GB and contains data back to 2008. Is it possible to prune this table to reduce the database size? Thanks Richard I am assuming you are using DPM 2007 SP1. tbl_TE_TaskTrail is supposed to have the entries only for the last one month tasks. Looks like that the Garbage collection is not happening. Can you please make sure that you have DPM 2007 SP1 and its latest QFE(http://www.microsoft.com/downloads/details.aspx?FamilyID=74ac7461-dbfe-4bc1-85a2-4d2948f30e42) installed on your DPM se...

How do I insert a letter into an existing word document?
I am working on a large document that I need to add several letters that are on our letterhead. If I cut and paste the letter, the letterhead header becomes skewed. This is just one issue I'm having with the document! Also need to know how to merge 3 separate documents together once I insert the letters that I need! Help! What do you mean by "skewed"? To preserve the data from the header of the document, you will need to insert it into a Section of its own separated from the rest of the document by Next page Section breaks before and after it with the headers...

Calling a global function from another entity
I hope someone can teel me what I'm doing wrong. I have code in the SalesOrderDetail entity. My Co-worker wrote some Tax & Freight calculation code in the SalesOrder entity. He put this code into a Function declared in the OnLoad event thusly: recalc = function() { // His Code } Now, I'm trying to call this function in my code on the Child SalesOrderDetail record using "window.opener" thusly: window.opener.crmForm.recalc(); But I'm getting errors when doing so. Am I missing something? Can I call a function from another entity in this manner?? Any help is...

Offline Database #5
Hy, I use the option : offline terminal database with RMS version 1.3R but I have some problems. I have create my offline database. I have define it to the Pos Administrator. The database online synchronize the database offline when I generate Z report ... on Pos. But I use custom table connected with the Item's table. This table contents item's price and it will not synchronize on the offline database. Is there any solution or a tips to solve this problem. Thanks Math I don't believe there is any way to make your added table synchronize. You could write your own sync...

Another simple formula??
a1= $2.73 b1=2 c1=6.5% d1=$5.89 Very simply I want to take the unit price (a1) * Qty (b1) and add the sales tax (c1) and have the result in one cell (d1). I can do it with helper column but would rather not. The answer should be $5.89. (d1) Tx wabbleknee, Your D1 figure is wrong. It should be in the $5.81 or $5.82 range based on how you rounded the result. Choose from the following formulas, and place one in Cell D1, drag and fill down the rest of the column: =ROUND(SUM($A1*$B1,$A1*B1*$C1),2) =ROUND(($A1*$B1)*(1+$C1),2) Will give you $5.81 =ROUNDUP(SUM($A1*$B1,$A1*B1*$C1),2) ...

Another Conditional formatting question
Hi all, date box, I want it to be red when it is older than a year. I have this set in my CF condition: Field value is Greater Than Now()-365 (red,bold) Well, it is red bold anyways. when i change the date to something LESS then a year old, it flashes normal quickly, but then goes back to red/bold. Thanks in advance! On Mon, 15 Feb 2010 09:58:02 -0800, Steph wrote: > Hi all, date box, I want it to be red when it is older than a year. > > I have this set in my CF condition: > > Field value is Greater Than Now()-365 (red,bold) > > Wel...