Re: Insert into Oracle from Access

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 26 Feb 2003 11:57:05 -0800
Message-ID: <3e5d1c11@news.victoria.tc.ca>

Randy Harris (randy.harris_at_nospam.net) wrote:
: Oliver, thanks so much for your reply, but please pardon my slowness to
: understand. In the example you've provided I can see how this would work to
: do the insert in the remote database. But how would I construct this to
: insert into a table in the remote database, records in a table in the local
: (Access) database?

you loop over the rows of the local table, and use the extracted data to build an insert statement. If you set up the insert statement correctly to reference access variables then you don't have to worry about quoting anything. I do not recall the vb syntax, so the example below is just pseudo code to show what I mean

: --
: Randy Harris

: "Oliver Demus" <oliver_at_demus-online.de> wrote in message
: news:62e59750.0302251840.27e95905_at_posting.google.com...
: > Hi there,
: > yes, you can do that with ADO in VBA:
: > here is a small example:
: > Connect these:
: > ' Microsoft ActivX Data Objects (Multi-Dimensional) 2.5
: > ' Microsoft ActivX Data Objects 2.5 Library
: > ' Microsoft ActivX Data Objects Recordset 2.5 Library
: > '
: > '
: > Function InsertData()
: >
: >
: > Dim conn As New ADODB.Connection
: > Dim rs As New ADODB.Recordset
: > conn.Open "DSN=<Database>;UID=<User>;PWD=<Password>"

	open local_query
	get_next_columns into var1, var2
	while (not end of local table)
	    insert_stmt = 'INSERT INTO TABLE VALUES(:var1, :var2)'
	    conn.Execute(insert_stmt)
	    get_next_columns into var1, var2
	wend



: >     conn.Close

: >

: > End Function

: >
: > Hope that helps

Hope that helps

The ms access help files have lots of examples of using the vb code to do this sort of thing. Received on Wed Feb 26 2003 - 13:57:05 CST