Database Efficiency

[McKirahan]

What is the most efficient way to build an extract database?

Specifically, I want to read, via ODBC, one non-MS-Access database table
with dozens of fields and hundreds of rows and update an initialized
MS-Access database table with only a couple of fields from all rows.

Is there a more efficient way than the following? Watch for word-wrap.

'*
'* Declare Constants
'*
Const cMDB = "MSAccess.mdb"
Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
'*
Const adCmdTable = &H0002
' Const adExecuteNoRecords = ?
Const adLockOptimistic = 3
Const adOpenKeySet = 1
'*
'* Declare Variables
'*
Dim strSQ1
strSQ1 = "SELECT CustomerNumber, CustomerName"
strSQ1 = strSQ1 & " FROM AR1_CustomerMaster"
Dim strSQ2
strSQ2 = "INSERT INTO AR1_CustomerMaster"
strSQ2 = strSQ2 & " (CustomerNumber, CustomerNumber)"
strSQ2 = strSQ2 & " VALUES (#1, #2)"
'*
'* Declare Objects
'*
Dim objAD1
Set objAD1 = CreateObject("ADODB.Connection")
objAD1.Open "ODBC_System_DSN"
Dim objRS1
Set objRS1 = objAD1.Execute(strSQ1)
'*
Dim objAD2
Set objAD2 = CreateObject("ADODB.Connection")
objAD2.Open cDSN & Server.MapPath(cMDB)
Dim objRS2
'*
'* Process Each Row
'*
Do Until objRS1.EOF
strSQ2 = Replace(strSQ2,"#1",objRS1("CustomerNumber"))
strSQ2 = Replace(strSQ2,"#2",objRS1("CustomerName"))
objAD2.Execute strSQ2, , 129 '= adExecuteNoRecords + adCmdText
objRS1.MoveNext
Loop
'*
'* Destroy Objects
'*
Set objRS1 = Nothing
Set objAD2 = Nothing
Set objAD1 = Nothing

The above (in part) is from [url]http://www.aspfaq.com/show.asp?id=2191[/url]


Would the following be better (doubtfully) than INSERT?

Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.Open "AR1_CustomerMaster", objAD2, adOpenKeySet,
adLockOptimistic, adCmdTable
objRS2.AddNew
objRS2("CustomerNumber") = objRS1("CustomerNumber")
objRS2("CustomerName") = objRS1("CustomerName")
objRS2.Update
Set objRS2 = Nothing


Is there a way to do the SELECT and INSERT in a single SQL statement?


Also, what is the value of "adExecuteNoRecords"?
It isn't in the include file "ADOVBS.INC".

Thanks in advance.

[Bob Barrows [MVP]]

McKirahan wrote:

> What is the most efficient way to build an extract database?
>
> Specifically, I want to read, via ODBC, one non-MS-Access database
> table with dozens of fields and hundreds of rows and update an
> initialized MS-Access database table with only a couple of fields
> from all rows.
>
> Is there a more efficient way than the following? Watch for
> word-wrap.
>
> '*
> '* Declare Constants
> '*
> Const cMDB = "MSAccess.mdb"
> Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
> '*
> Const adCmdTable = &H0002
> ' Const adExecuteNoRecords = ?

Const adExecuteNoRecords = 128

> Const adLockOptimistic = 3
> Const adOpenKeySet = 1
> '*
> '* Declare Variables
> '*
> Dim strSQ1
> strSQ1 = "SELECT CustomerNumber, CustomerName"
> strSQ1 = strSQ1 & " FROM AR1_CustomerMaster"
> Dim strSQ2
> strSQ2 = "INSERT INTO AR1_CustomerMaster"
> strSQ2 = strSQ2 & " (CustomerNumber, CustomerNumber)"
> strSQ2 = strSQ2 & " VALUES (#1, #2)"
> '*
> '* Declare Objects
> '*
> Dim objAD1
> Set objAD1 = CreateObject("ADODB.Connection")
> objAD1.Open "ODBC_System_DSN"
> Dim objRS1
> Set objRS1 = objAD1.Execute(strSQ1)
> '*
> Dim objAD2
> Set objAD2 = CreateObject("ADODB.Connection")
> objAD2.Open cDSN & Server.MapPath(cMDB)
> Dim objRS2
> '*
> '* Process Each Row
> '*
> Do Until objRS1.EOF
> strSQ2 = Replace(strSQ2,"#1",objRS1("CustomerNumber"))
> strSQ2 = Replace(strSQ2,"#2",objRS1("CustomerName"))
> objAD2.Execute strSQ2, , 129 '= adExecuteNoRecords + adCmdText
> objRS1.MoveNext
> Loop
> '*
> '* Destroy Objects
> '*
> Set objRS1 = Nothing
> Set objAD2 = Nothing
> Set objAD1 = Nothing
>
> The above (in part) is from [url]http://www.aspfaq.com/show.asp?id=2191[/url]
>
>
> Would the following be better (doubtfully) than INSERT?

No, INSERT will always be faster.

>
> Set objRS2 = Server.CreateObject("ADODB.Recordset")
> objRS2.Open "AR1_CustomerMaster", objAD2, adOpenKeySet,
> adLockOptimistic, adCmdTable
> objRS2.AddNew
> objRS2("CustomerNumber") = objRS1("CustomerNumber")
> objRS2("CustomerName") = objRS1("CustomerName")
> objRS2.Update
> Set objRS2 = Nothing
>
>
> Is there a way to do the SELECT and INSERT in a single SQL statement?

Yes, there are two ways:

1. You can create a linked table in your Access database and do a simple:
strSQ2 = "INSERT INTO AR1_CustomerMaster"
strSQ2 = strSQ2 & " (CustomerNumber, CustomerNumber)"
strSQ2 = strSQ2 & " SELECT CustomerNumber, CustomerName"
strSQ2 = strSQ2 & " FROM AR1_CustomerMaster_lnk"



2. You can use an IN clause in your SELECT statement:

Dim strSQ2
strSQ2 = "INSERT INTO AR1_CustomerMaster"
strSQ2 = strSQ2 & " (CustomerNumber, CustomerNumber)"
strSQ2 = strSQ2 & " SELECT CustomerNumber, CustomerName"
strSQ2 = strSQ2 & " FROM AR1_CustomerMaster "
strSQ2 = strSQ2 & " IN '' [ODBC; DSN=ODBC_System_DSN;]"

>
>
> Also, what is the value of "adExecuteNoRecords"?

When you execute a statement that does not return records, you need to tell
the Command object (yes, a Command object is being used by ADO here) to skip
the creation of a recordset object. If you do not use "adExecuteNoRecords",
it will create a recordset object even though your query does not return any
records.


Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"