Re: SELECT INTO an external database

Greetings,

Here is another way to manipulate data to/from an External Database
using ADO:

Dim cmd As New ADODB.Command, RS1 As New ADODB.Recordset
Dim strConn As String

strConn = "Provider =Microsoft.Jet.OLEDB.4.0; " _
& " Persist Security Info=false;" _
& "Data Source=C:\someDir\ExternalDB.mdb"

cmd.ActiveConnection = strConn
cmd.CommandType = adCmdText
cmd.CommandText = "Insert Into yourExternalTbl(fld1, fld2, fld3) Select
'test1', 'test2', 'test3'"
cmd.Execute
cmd.ActiveConnection.Close

Here the example assumes there is a table called yourExternalTbl with 3
text fields called fld1, fld2, fld3

ADO can't do bulk inserts to an External DB, only row by row. You can
also use an ADODB Recordset object, but the command object is more
efficient and easier to use. For Bulk Insert to an External DB from
Access use

DoCmd.RunDql "Insert Into yourExternaltbl In 'C:\someDir\ExternalDB.mdb'
Select * from yourLocalTbl"

Rich

*** Sent via Developersdex http://www.developersdex.com ***
.

Relevant Pages

  • Re: HELP with adodb.command object, PLEASE!!!
    ... Which version of ADO are you using and which version of ADO do you have on ... Dim cmd As ADODB.Command ... > system test and/or production machines, ... > support expected interface." ...
    (microsoft.public.vb.database.ado)
  • Re: Bad variable type exception
    ... Why are you using ADO instead of ADO.NET? ... This is a forum for ADO.NET ... > I have made this function to create a command object to be used to call a ... > Dim cmd As New ADODB.Command ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Access2003 - sql server 2005, db.execute times out updating sql server data
    ... effectively because ADO does not hold the connection open continuously ... Dim cmd As New ADODB.Command ... cmd.CommandType = adCmdText ... set cmd = new adodb.command ...
    (comp.databases.ms-access)
  • Re: Linked SQL tables/popup form problem
    ... You populate it as needed with ADO. ... Dim cmd As New ADODB.Command, ... Dim RS1 As DAO.Recordset, i As Integer ... Set RS1 = CurrentDB.OpenRecordset ...
    (comp.databases.ms-access)