Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 26th, 2005, 08:07 AM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default Insert ODBC Values into Table

I would like to insert the results of in an ODBC recordset into an Access Table.

Currently I cycle through each value and use "Insert Into.. Value...".

However I feel it would be much cleaner if I could just use "Insert Into.. Select..."

- My query is how do I specify the query that builds my recordset into the Insert Statement? Is there a way of passing the connection details within the sql query?
 
Old January 26th, 2005, 08:21 AM
Authorized User
 
Join Date: May 2004
Location: Bucharest, , Romania.
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Ben,

I think the easiest way to write the SQL statement is to build an Append Query in Access (Design View is simplest), then edit the SQL statment, copy-paste it into the code and eventually make the necessary changes.
I asume you use ADODB, so you can pass the SQL statement to the connection.Execute method

Mihai

 
Old January 26th, 2005, 09:50 AM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Mihai,

Thanks for your reply.

I've already written the Select statement I need, however I'm unsure if it will work in an insert query as it is.

For example my query currently reads

sConnect = "PROVIDER=MSDASQL;DSN=xDb"
Set db = New Connection
db.Open sConnect

Set rec = New ADODB.Recordset
sSQL = "Select Col1, Col2.... From tblx"

rec.Open sSQL, db

Do While Not rec.EOF
sSQL = "Insert Into tbl (col1, col2, ...) Values (" & rec(0) & ", "
& rec(1) ... & ")"

CurrentDb.Execute sSQL

rec.MoveNext
Loop

rec.Close

I would like to change this so it reads:

sSQL = "Insert Into tbl (col1, col2, ...) Select Col1, Col2.... From tblx"

CurrentDb.Execute sSQL

rec.Close

The trouble is how do I define the Select query in the second example is via ODBC?
Due to a bug in the driver I can't link the table into the database and I'm adverse to creating a pass-through query in resides in the database.
 
Old January 26th, 2005, 01:12 PM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Anyone any ideas :(:)




Similar Threads
Thread Thread Starter Forum Replies Last Post
ODBC linked table showing old data (not refreshing d-fxt Access 3 November 14th, 2007 05:56 PM
insert multiple records into a table from values Deepak Chauhan Oracle 3 May 12th, 2006 10:35 PM
ODBC Linked Table shows #Deleted in all fields Darron Michael Access 4 December 16th, 2005 03:00 PM
ODBC - insert on a linked field - [microsoft][ODBC chimp MySQL 0 April 7th, 2005 04:44 AM
JSP form values to Oracle table (Insert) gokul_blr JSP Basics 1 August 17th, 2004 12:59 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.