Performance issue - Access 2000 using VB6 and ADO 2.1 with SQL

Performance issue - Access 2000 using VB6 and ADO 2.1 with SQL

Post by Duncan Lain » Sat, 11 Dec 1999 04:00:00



Hi Guys

I have a largish Access 2000 database with some tables around 50000 records
that are causing me great concern as far as update times go.  Currently I am
using Access as the Database but with VB6 sp3 and ADO 2.1 as my method of
access. All accessing of data is done through code using SQL.
I import a text file weekly into a weeklyImport table and then cycle through
the type of data with "A" for new lines, "D" for deletions and "C" for
changes - the "C" are the majority.
Typically with 2000 lines I would have about 80 new, 60 Delete and the rest
changes against a product table of 50,000 records.

I am currently using SQL to query the database and return subsets, so I
would return all the ADD records and then process them, followed by creating
a subset for delete records and then process those.

My problem is the time it is taking to perform these updates seems long at
nearly a second each for the INSERTS into my products table.
and the DELETE process is only flagging each record with the word DELETE
into a field but that is still processing at only about 2-3 records a second
being modified.

My basic approach is to use a SELECT * WHERE Type = "A" from my weekly
import table to create the subsets (code below) and then to use a while loop
to traverse the recordset and perform the INSERT after a SELECT from product
to see if it exists and validation for each member of the recordset.
With the DELETES I use a SELECT followed by a SELECT form products for the
one affected and then an UPDATE command to modify the record.

Could someone please look at my code and see if they think my approach is OK
or can you suggest some other appraoch to achieve better performance..

TIA

Duncan Laing

mail_nosp...@dunlaing.mtx.net
please reply to newsgroup

' a global connection declared and then initialized in my sub main
Global cn As New ADODB.Connection

Sub main()
    '
    ' do set up routines splash etc
    '
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; _
        DataSource=c:\POS\DATA\DATABASE.MDB; _
        Persist Security Info=False"
    cn.Open
    '
    ' hide splash etc
    '
    MDIForm1.Show ' contains all my work forms
End Sub

in declarations of my masterfile form

    Public rsWeeklyImport As New ADODB.Recordset
    Public rsProducts As New ADODB.Recordset

Then in form load

    rsWeeklyImport.ActiveConnection = cn
    rsWeeklyImport.CursorType = adOpenKeyset
    rsWeeklyImport.LockType = adLockOptimistic

    rsProducts.ActiveConnection = cn
    rsProducts.CursorType = adOpenKeyset

Private Sub plProcessNewLines()
    Dim slSQLString As String

    If rsWeeklyImport.State Then rsWeeklyImport.Close

    slSQLString = "SELECT * from WeeklyImport WHERE Type = 'A' " 'AND
Activated = 0
    rsWeeklyImport.Source = slSQLString
    rsWeeklyImport.Open

    With rsWeeklyImport
        If .RecordCount > 0 Then
            .MoveFirst
            'loop through the new line recordset
            While Not .EOF
                If (flValidateWeeklyImport) Then 'validate checks numeric
fields against limits

                    plUpdateProducts
                    ' creates product record that can now be manipulated
across the module

                    ' found product & weeklyImport records at this point

                    ' next lines sets my processed flag to true
                    slSQLString = "UPDATE WeeklyImport Set Activated = -1
WHERE ProductID = " & !ProductID
                    cn.Execute slSQLString, , adCmdText
                'Else
                    'plWriteReject
                End If
                .MoveNext
            Wend
        End If
    End With ' rsWeeklyImport

    txtImpNewLines = llNumRecords

End Sub

Private Sub plUpdateProducts()
    Dim slSQLString As String

    With rsWeeklyImport  'rsWeeklyImport is the recordset I am traversing

        'check if product exists

        If rsProducts.State Then rsProducts.Close

        slSQLString = "SELECT * FROM Products WHERE ProductID = " &
!ProductID
        rsProducts.Source = slSQLString
        rsProducts.Open

 'there is little validation on the SQL generation because the data was
fixed as it was stored into WeeklyImports
        If rsProducts.RecordCount > 0 Then
            ' if product record exists then update the order code field
            slSQLString = "UPDATE Products SET OrderCode = '" & !OrderCode &
"' WHERE productID = " & !ProductID

            cn.Execute slSQLString, , adCmdText

            ' and then change the import record from new line to a change
detail record this will then check promo pricing as well

            If rsProducts!Aisle > 0 Then
                slSQLString = "UPDATE WeeklyImport SET Type = 'C' WHERE
productID = " & !ProductID
                cn.Execute slSQLString, , adCmdText
            End If
        Else
     ' I think this is my slow one
            slSQLString = "INSERT INTO Products
(ProductID,SubDeptID,Description,ShortDescription,OrderCode,UnitCost,DavidsC
ost,"
            slSQLString = slSQLString &
"RetailPrice,CompRetail,DiscRetail,SuperRetail,ServRetail,ConvRetail,PackSiz
e,SupplierID,"
            slSQLString = slSQLString &
"NumberOfLabels,Aisle,Bay,TaxCode,TaxRate,DavidsClassificationCode) VALUES
("
            slSQLString = slSQLString & !ProductID & ","
            slSQLString = slSQLString & !SubDepartment & ","
            slSQLString = slSQLString & "'" & !Description & "',"
            slSQLString = slSQLString & "'" & !ShortDescription & "',"
            slSQLString = slSQLString & "'" & !OrderCode & "',"
            slSQLString = slSQLString & !UnitCost & ","
            slSQLString = slSQLString & !UnitCost & ","
            slSQLString = slSQLString & !SuperRetail & ","
            slSQLString = slSQLString & !CompRetail & ","
            slSQLString = slSQLString & !DiscRetail & ","
            slSQLString = slSQLString & !SuperRetail & ","
            slSQLString = slSQLString & !ServRetail & ","
            slSQLString = slSQLString & !ConvRetail & ","
            slSQLString = slSQLString & !packSize & ","
            slSQLString = slSQLString & "1,"     'this is Supplier ID and
must be set to 1
            slSQLString = slSQLString & "1,"      ' default number of labels
            slSQLString = slSQLString & "0,"      ' aisle = 0 till added to
store
            slSQLString = slSQLString & "0,"      ' bay = zero
            slSQLString = slSQLString & "'" & !TaxCode & "',"
            slSQLString = slSQLString & "0,"        ' Tax Rate
            slSQLString = slSQLString & "'" & !RetailClassificationCode &
"');"

            cn.Execute slSQLString, , adCmdText
        End If

    End With 'rsWeeklyImport
End Sub

Private Sub plProcessDeletedLines()
    Dim slSQLString As String

    If rsWeeklyImport.State Then rsWeeklyImport.Close

    slSQLString = "Select * from WeeklyImport WHERE Type = 'D' AND Activated
= 0"
    rsWeeklyImport.Source = slSQLString
    rsWeeklyImport.Open

    With rsWeeklyImport
        If .RecordCount > 0 Then
            .MoveFirst
            'loop through the new line recordset
            While Not .EOF

                If rsProducts.State Then rsProducts.Close

                slSQLString = "SELECT * FROM Products WHERE ProductID = " &
!ProductID 'the passed in  record ID
                rsProducts.Source = slSQLString
                rsProducts.Open

                If rsProducts.RecordCount > 0 Then
                    ' if product record exists then update the order code
field
                    slSQLString = "UPDATE Products SET OrderCode =
'DELETE',"
                    slSQLString = slSQLString + "LastModified = '" &
Format(Now(), "dd/mm/yyyy") & "' WHERE productID = " & !ProductID
                    cn.Execute slSQLString, , adCmdText

                    slSQLString = "UPDATE WeeklyImport Set Activated = -1
WHERE ProductID = " & !ProductID
                    cn.Execute slSQLString, , adCmdText
                Else
                    msErrorMessage = "Product to be Deleted was not on File
APN = " & !ProductID
                    plWriteReject
                End If
                .CancelUpdate
                .MoveNext
            Wend
        End If
    End With ' rsWeeklyImport The recordsets are closed in form unload
End Sub

 
 
 

Performance issue - Access 2000 using VB6 and ADO 2.1 with SQL

Post by Joe Spanice » Sat, 11 Dec 1999 04:00:00


From your info it seems that the database design itself maybe the problem.
Have you looked to see if setting up indexs on the tables would improve the
performance. Adding indexes keeps the rows in the tables sorted on the index
column allowing your queries to search through the tables more quickly.

> Hi Guys

> I have a largish Access 2000 database with some tables around 50000 records
> that are causing me great concern as far as update times go.  Currently I am
> using Access as the Database but with VB6 sp3 and ADO 2.1 as my method of
> access. All accessing of data is done through code using SQL.
> I import a text file weekly into a weeklyImport table and then cycle through
> the type of data with "A" for new lines, "D" for deletions and "C" for
> changes - the "C" are the majority.
> Typically with 2000 lines I would have about 80 new, 60 Delete and the rest
> changes against a product table of 50,000 records.

> I am currently using SQL to query the database and return subsets, so I
> would return all the ADD records and then process them, followed by creating
> a subset for delete records and then process those.

> My problem is the time it is taking to perform these updates seems long at
> nearly a second each for the INSERTS into my products table.
> and the DELETE process is only flagging each record with the word DELETE
> into a field but that is still processing at only about 2-3 records a second
> being modified.

> My basic approach is to use a SELECT * WHERE Type = "A" from my weekly
> import table to create the subsets (code below) and then to use a while loop
> to traverse the recordset and perform the INSERT after a SELECT from product
> to see if it exists and validation for each member of the recordset.
> With the DELETES I use a SELECT followed by a SELECT form products for the
> one affected and then an UPDATE command to modify the record.


 
 
 

Performance issue - Access 2000 using VB6 and ADO 2.1 with SQL

Post by Jaso » Sun, 12 Dec 1999 04:00:00


My experience is that deleting and editing work great as SQL but I have
achieved
better performance but using .AddNew instead of INSERT INTO.
This may help some speed, also check your indexes.

Jason

"Duncan Laing" <dunla...@dove.net.au> wrote in message

news:38511433@curlew.mtx.net.au...
> Hi Guys

> I have a largish Access 2000 database with some tables around 50000
records
> that are causing me great concern as far as update times go.  Currently I
am
> using Access as the Database but with VB6 sp3 and ADO 2.1 as my method of
> access. All accessing of data is done through code using SQL.
> I import a text file weekly into a weeklyImport table and then cycle
through
> the type of data with "A" for new lines, "D" for deletions and "C" for
> changes - the "C" are the majority.
> Typically with 2000 lines I would have about 80 new, 60 Delete and the
rest
> changes against a product table of 50,000 records.

> I am currently using SQL to query the database and return subsets, so I
> would return all the ADD records and then process them, followed by
creating
> a subset for delete records and then process those.

> My problem is the time it is taking to perform these updates seems long at
> nearly a second each for the INSERTS into my products table.
> and the DELETE process is only flagging each record with the word DELETE
> into a field but that is still processing at only about 2-3 records a
second
> being modified.

> My basic approach is to use a SELECT * WHERE Type = "A" from my weekly
> import table to create the subsets (code below) and then to use a while
loop
> to traverse the recordset and perform the INSERT after a SELECT from
product
> to see if it exists and validation for each member of the recordset.
> With the DELETES I use a SELECT followed by a SELECT form products for the
> one affected and then an UPDATE command to modify the record.

> Could someone please look at my code and see if they think my approach is
OK
> or can you suggest some other appraoch to achieve better performance..

> TIA

> Duncan Laing

> mail_nosp...@dunlaing.mtx.net
> please reply to newsgroup

> ' a global connection declared and then initialized in my sub main
> Global cn As New ADODB.Connection

> Sub main()
>     '
>     ' do set up routines splash etc
>     '
>     cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; _
>         DataSource=c:\POS\DATA\DATABASE.MDB; _
>         Persist Security Info=False"
>     cn.Open
>     '
>     ' hide splash etc
>     '
>     MDIForm1.Show ' contains all my work forms
> End Sub

> in declarations of my masterfile form

>     Public rsWeeklyImport As New ADODB.Recordset
>     Public rsProducts As New ADODB.Recordset

> Then in form load

>     rsWeeklyImport.ActiveConnection = cn
>     rsWeeklyImport.CursorType = adOpenKeyset
>     rsWeeklyImport.LockType = adLockOptimistic

>     rsProducts.ActiveConnection = cn
>     rsProducts.CursorType = adOpenKeyset

> Private Sub plProcessNewLines()
>     Dim slSQLString As String

>     If rsWeeklyImport.State Then rsWeeklyImport.Close

>     slSQLString = "SELECT * from WeeklyImport WHERE Type = 'A' " 'AND
> Activated = 0
>     rsWeeklyImport.Source = slSQLString
>     rsWeeklyImport.Open

>     With rsWeeklyImport
>         If .RecordCount > 0 Then
>             .MoveFirst
>             'loop through the new line recordset
>             While Not .EOF
>                 If (flValidateWeeklyImport) Then 'validate checks numeric
> fields against limits

>                     plUpdateProducts
>                     ' creates product record that can now be manipulated
> across the module

>                     ' found product & weeklyImport records at this point

>                     ' next lines sets my processed flag to true
>                     slSQLString = "UPDATE WeeklyImport Set Activated = -1
> WHERE ProductID = " & !ProductID
>                     cn.Execute slSQLString, , adCmdText
>                 'Else
>                     'plWriteReject
>                 End If
>                 .MoveNext
>             Wend
>         End If
>     End With ' rsWeeklyImport

>     txtImpNewLines = llNumRecords

> End Sub

> Private Sub plUpdateProducts()
>     Dim slSQLString As String

>     With rsWeeklyImport  'rsWeeklyImport is the recordset I am traversing

>         'check if product exists

>         If rsProducts.State Then rsProducts.Close

>         slSQLString = "SELECT * FROM Products WHERE ProductID = " &
> !ProductID
>         rsProducts.Source = slSQLString
>         rsProducts.Open

>  'there is little validation on the SQL generation because the data was
> fixed as it was stored into WeeklyImports
>         If rsProducts.RecordCount > 0 Then
>             ' if product record exists then update the order code field
>             slSQLString = "UPDATE Products SET OrderCode = '" & !OrderCode
&
> "' WHERE productID = " & !ProductID

>             cn.Execute slSQLString, , adCmdText

>             ' and then change the import record from new line to a change
> detail record this will then check promo pricing as well

>             If rsProducts!Aisle > 0 Then
>                 slSQLString = "UPDATE WeeklyImport SET Type = 'C' WHERE
> productID = " & !ProductID
>                 cn.Execute slSQLString, , adCmdText
>             End If
>         Else
>      ' I think this is my slow one
>             slSQLString = "INSERT INTO Products

(ProductID,SubDeptID,Description,ShortDescription,OrderCode,UnitCost,DavidsC
> ost,"
>             slSQLString = slSQLString &

"RetailPrice,CompRetail,DiscRetail,SuperRetail,ServRetail,ConvRetail,PackSiz

- Show quoted text -

> e,SupplierID,"
>             slSQLString = slSQLString &
> "NumberOfLabels,Aisle,Bay,TaxCode,TaxRate,DavidsClassificationCode) VALUES
> ("
>             slSQLString = slSQLString & !ProductID & ","
>             slSQLString = slSQLString & !SubDepartment & ","
>             slSQLString = slSQLString & "'" & !Description & "',"
>             slSQLString = slSQLString & "'" & !ShortDescription & "',"
>             slSQLString = slSQLString & "'" & !OrderCode & "',"
>             slSQLString = slSQLString & !UnitCost & ","
>             slSQLString = slSQLString & !UnitCost & ","
>             slSQLString = slSQLString & !SuperRetail & ","
>             slSQLString = slSQLString & !CompRetail & ","
>             slSQLString = slSQLString & !DiscRetail & ","
>             slSQLString = slSQLString & !SuperRetail & ","
>             slSQLString = slSQLString & !ServRetail & ","
>             slSQLString = slSQLString & !ConvRetail & ","
>             slSQLString = slSQLString & !packSize & ","
>             slSQLString = slSQLString & "1,"     'this is Supplier ID and
> must be set to 1
>             slSQLString = slSQLString & "1,"      ' default number of
labels
>             slSQLString = slSQLString & "0,"      ' aisle = 0 till added
to
> store
>             slSQLString = slSQLString & "0,"      ' bay = zero
>             slSQLString = slSQLString & "'" & !TaxCode & "',"
>             slSQLString = slSQLString & "0,"        ' Tax Rate
>             slSQLString = slSQLString & "'" & !RetailClassificationCode &
> "');"

>             cn.Execute slSQLString, , adCmdText
>         End If

>     End With 'rsWeeklyImport
> End Sub

> Private Sub plProcessDeletedLines()
>     Dim slSQLString As String

>     If rsWeeklyImport.State Then rsWeeklyImport.Close

>     slSQLString = "Select * from WeeklyImport WHERE Type = 'D' AND
Activated
> = 0"
>     rsWeeklyImport.Source = slSQLString
>     rsWeeklyImport.Open

>     With rsWeeklyImport
>         If .RecordCount > 0 Then
>             .MoveFirst
>             'loop through the new line recordset
>             While Not .EOF

>                 If rsProducts.State Then rsProducts.Close

>                 slSQLString = "SELECT * FROM Products WHERE ProductID = "
&
> !ProductID 'the passed in  record ID
>                 rsProducts.Source = slSQLString
>                 rsProducts.Open

>                 If rsProducts.RecordCount > 0 Then
>                     ' if product record exists then update the order code
> field
>                     slSQLString = "UPDATE Products SET OrderCode =
> 'DELETE',"
>                     slSQLString = slSQLString + "LastModified = '" &
> Format(Now(), "dd/mm/yyyy") & "' WHERE productID = " & !ProductID
>                     cn.Execute slSQLString, , adCmdText

>                     slSQLString = "UPDATE WeeklyImport Set Activated = -1
> WHERE ProductID = " & !ProductID
>                     cn.Execute slSQLString, , adCmdText
>                 Else
>                     msErrorMessage = "Product to be Deleted was not on
File
> APN = " & !ProductID
>                     plWriteReject
>                 End If
>                 .CancelUpdate
>                 .MoveNext
>             Wend
>         End If
>     End With ' rsWeeklyImport The recordsets are closed in form unload
> End Sub

 
 
 

Performance issue - Access 2000 using VB6 and ADO 2.1 with SQL

Post by Duncan Lain » Sun, 12 Dec 1999 04:00:00


Joe I was wondering

If I have my ProductID field set as primary key then is that equivalent to
having an index set to ProductID.
Most of the work in this part of my app is against the productID of the the
Product table.

By the way I nearly doubled the speed of my updates by replacing a SELECT *
from Products ... to
SELECT field1,field2,field3,....field15 from products WHERE ... this
advantage was because the Products table has about 70 fields in each record
and yes they are all unique to each product.

thanks for your reply

Duncan


>From your info it seems that the database design itself maybe the problem.
>Have you looked to see if setting up indexs on the tables would improve the
>performance. Adding indexes keeps the rows in the tables sorted on the
index
>column allowing your queries to search through the tables more quickly.

 
 
 

Performance issue - Access 2000 using VB6 and ADO 2.1 with SQL

Post by John Wrig » Tue, 14 Dec 1999 04:00:00


This may be a dumb question, but couldn't you do a lot of this work using
JOIN's instead of looping through recordsets?

In article <3851f...@newsserver1.picknowl.com.au>, jstangro...@picknowl.com.au
says...

>My experience is that deleting and editing work great as SQL but I have
>achieved
>better performance but using .AddNew instead of INSERT INTO.
>This may help some speed, also check your indexes.

>Jason

>"Duncan Laing" <dunla...@dove.net.au> wrote in message
>news:38511433@curlew.mtx.net.au...
>> Hi Guys

>> I have a largish Access 2000 database with some tables around 50000
>records
>> that are causing me great concern as far as update times go.  Currently I
>am
>> using Access as the Database but with VB6 sp3 and ADO 2.1 as my method of
>> access. All accessing of data is done through code using SQL.
>> I import a text file weekly into a weeklyImport table and then cycle
>through
>> the type of data with "A" for new lines, "D" for deletions and "C" for
>> changes - the "C" are the majority.
>> Typically with 2000 lines I would have about 80 new, 60 Delete and the
>rest
>> changes against a product table of 50,000 records.

>> I am currently using SQL to query the database and return subsets, so I
>> would return all the ADD records and then process them, followed by
>creating
>> a subset for delete records and then process those.

>> My problem is the time it is taking to perform these updates seems long at
>> nearly a second each for the INSERTS into my products table.
>> and the DELETE process is only flagging each record with the word DELETE
>> into a field but that is still processing at only about 2-3 records a
>second
>> being modified.

>> My basic approach is to use a SELECT * WHERE Type = "A" from my weekly
>> import table to create the subsets (code below) and then to use a while
>loop
>> to traverse the recordset and perform the INSERT after a SELECT from
>product
>> to see if it exists and validation for each member of the recordset.
>> With the DELETES I use a SELECT followed by a SELECT form products for the
>> one affected and then an UPDATE command to modify the record.

>> Could someone please look at my code and see if they think my approach is
>OK
>> or can you suggest some other appraoch to achieve better performance..

>> TIA

>> Duncan Laing

>> mail_nosp...@dunlaing.mtx.net
>> please reply to newsgroup

>> ' a global connection declared and then initialized in my sub main
>> Global cn As New ADODB.Connection

>> Sub main()
>>     '
>>     ' do set up routines splash etc
>>     '
>>     cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; _
>>         DataSource=c:\POS\DATA\DATABASE.MDB; _
>>         Persist Security Info=False"
>>     cn.Open
>>     '
>>     ' hide splash etc
>>     '
>>     MDIForm1.Show ' contains all my work forms
>> End Sub

>> in declarations of my masterfile form

>>     Public rsWeeklyImport As New ADODB.Recordset
>>     Public rsProducts As New ADODB.Recordset

>> Then in form load

>>     rsWeeklyImport.ActiveConnection = cn
>>     rsWeeklyImport.CursorType = adOpenKeyset
>>     rsWeeklyImport.LockType = adLockOptimistic

>>     rsProducts.ActiveConnection = cn
>>     rsProducts.CursorType = adOpenKeyset

>> Private Sub plProcessNewLines()
>>     Dim slSQLString As String

>>     If rsWeeklyImport.State Then rsWeeklyImport.Close

>>     slSQLString = "SELECT * from WeeklyImport WHERE Type = 'A' " 'AND
>> Activated = 0
>>     rsWeeklyImport.Source = slSQLString
>>     rsWeeklyImport.Open

>>     With rsWeeklyImport
>>         If .RecordCount > 0 Then
>>             .MoveFirst
>>             'loop through the new line recordset
>>             While Not .EOF
>>                 If (flValidateWeeklyImport) Then 'validate checks numeric
>> fields against limits

>>                     plUpdateProducts
>>                     ' creates product record that can now be manipulated
>> across the module

>>                     ' found product & weeklyImport records at this point

>>                     ' next lines sets my processed flag to true
>>                     slSQLString = "UPDATE WeeklyImport Set Activated = -1
>> WHERE ProductID = " & !ProductID
>>                     cn.Execute slSQLString, , adCmdText
>>                 'Else
>>                     'plWriteReject
>>                 End If
>>                 .MoveNext
>>             Wend
>>         End If
>>     End With ' rsWeeklyImport

>>     txtImpNewLines = llNumRecords

>> End Sub

>> Private Sub plUpdateProducts()
>>     Dim slSQLString As String

>>     With rsWeeklyImport  'rsWeeklyImport is the recordset I am traversing

>>         'check if product exists

>>         If rsProducts.State Then rsProducts.Close

>>         slSQLString = "SELECT * FROM Products WHERE ProductID = " &
>> !ProductID
>>         rsProducts.Source = slSQLString
>>         rsProducts.Open

>>  'there is little validation on the SQL generation because the data was
>> fixed as it was stored into WeeklyImports
>>         If rsProducts.RecordCount > 0 Then
>>             ' if product record exists then update the order code field
>>             slSQLString = "UPDATE Products SET OrderCode = '" & !OrderCode
>&
>> "' WHERE productID = " & !ProductID

>>             cn.Execute slSQLString, , adCmdText

>>             ' and then change the import record from new line to a change
>> detail record this will then check promo pricing as well

>>             If rsProducts!Aisle > 0 Then
>>                 slSQLString = "UPDATE WeeklyImport SET Type = 'C' WHERE
>> productID = " & !ProductID
>>                 cn.Execute slSQLString, , adCmdText
>>             End If
>>         Else
>>      ' I think this is my slow one
>>             slSQLString = "INSERT INTO Products

>(ProductID,SubDeptID,Description,ShortDescription,OrderCode,UnitCost,DavidsC
>> ost,"
>>             slSQLString = slSQLString &

>"RetailPrice,CompRetail,DiscRetail,SuperRetail,ServRetail,ConvRetail,PackSiz
>> e,SupplierID,"
>>             slSQLString = slSQLString &
>> "NumberOfLabels,Aisle,Bay,TaxCode,TaxRate,DavidsClassificationCode) VALUES
>> ("
>>             slSQLString = slSQLString & !ProductID & ","
>>             slSQLString = slSQLString & !SubDepartment & ","
>>             slSQLString = slSQLString & "'" & !Description & "',"
>>             slSQLString = slSQLString & "'" & !ShortDescription & "',"
>>             slSQLString = slSQLString & "'" & !OrderCode & "',"
>>             slSQLString = slSQLString & !UnitCost & ","
>>             slSQLString = slSQLString & !UnitCost & ","
>>             slSQLString = slSQLString & !SuperRetail & ","
>>             slSQLString = slSQLString & !CompRetail & ","
>>             slSQLString = slSQLString & !DiscRetail & ","
>>             slSQLString = slSQLString & !SuperRetail & ","
>>             slSQLString = slSQLString & !ServRetail & ","
>>             slSQLString = slSQLString & !ConvRetail & ","
>>             slSQLString = slSQLString & !packSize & ","
>>             slSQLString = slSQLString & "1,"     'this is Supplier ID and
>> must be set to 1
>>             slSQLString = slSQLString & "1,"      ' default number of
>labels
>>             slSQLString = slSQLString & "0,"      ' aisle = 0 till added
>to
>> store
>>             slSQLString = slSQLString & "0,"      ' bay = zero
>>             slSQLString = slSQLString & "'" & !TaxCode & "',"
>>             slSQLString = slSQLString & "0,"        ' Tax Rate
>>             slSQLString = slSQLString & "'" & !RetailClassificationCode &
>> "');"

>>             cn.Execute slSQLString, , adCmdText
>>         End If

>>     End With 'rsWeeklyImport
>> End Sub

>> Private Sub plProcessDeletedLines()
>>     Dim slSQLString As String

>>     If rsWeeklyImport.State Then rsWeeklyImport.Close

>>     slSQLString = "Select * from WeeklyImport WHERE Type = 'D' AND
>Activated
>> = 0"
>>     rsWeeklyImport.Source = slSQLString
>>     rsWeeklyImport.Open

>>     With rsWeeklyImport
>>         If .RecordCount > 0 Then
>>             .MoveFirst
>>             'loop through the new line recordset
>>             While Not .EOF

>>                 If rsProducts.State Then rsProducts.Close

>>                 slSQLString = "SELECT * FROM Products WHERE ProductID = "
>&
>> !ProductID 'the passed in  record ID
>>                 rsProducts.Source = slSQLString
>>                 rsProducts.Open

>>                 If rsProducts.RecordCount > 0 Then
>>                     ' if product record exists then update the order code
>> field
>>                     slSQLString = "UPDATE Products SET OrderCode =
>> 'DELETE',"
>>                     slSQLString = slSQLString + "LastModified = '" &
>> Format(Now(), "dd/mm/yyyy") & "' WHERE productID = " & !ProductID
>>                     cn.Execute slSQLString, , adCmdText

>>                     slSQLString = "UPDATE WeeklyImport Set Activated = -1
>> WHERE ProductID = " & !ProductID
>>                     cn.Execute slSQLString, , adCmdText
>>                 Else
>>                     msErrorMessage = "Product to be Deleted was not on
>File
>> APN = " & !ProductID
>>                     plWriteReject
>>                 End If
>>                 .CancelUpdate
>>                 .MoveNext
>>             Wend
>>         End If
>>     End With ' rsWeeklyImport The recordsets are closed in form unload
>> End Sub

 
 
 

1. VB6, ADO 2.1 and MSDE (Access 2000)

A copy of the MSDE (Msft. Data Engine) is provided with
Access 2000 (separate installation, however) and is used
by A2000 for ADP (Active Data Projects).  MSDE is
basically the desktop version of SQL Server 7,

My question is; How does VB 6 interface with the MSDE???

1) When you install the MSDE, is a Reference automatically
   added to the Project|References list in VB6?

2) What is the Data Provider string used when opening an
   ADODB Connection to the MSDE in VB6.

3) Other questions/concerns?

Perhaps the biggest freebie ever from Microsoft is the
MSDE (but it is only free until you realize that you need
to upgrade to Windows NT and SQL Server -- it is sort
of like pushers handing out samples -- Microsoft has a
lot of USERS and they know how to get more USERS).

The MSDE has a lot of advantages of the JET 4.0 engine.
You can move a VB app. developed for the MSDE to SQL Server
with practically no changes to the app.  It does away
with page locking -- it employs row level locks in most
cases.  It uses the Transact-SQL syntax for SQL statements.
And, so on.

But, I have been unable to find any treatment in the VB
Docs./Books and the Access 2000 Books (I ordered A2000
today) that ties VB6 and the MSDE together.

2. GROUP BY on a field of data type date/time

3. accessing Access 2000 Report using VB6 ADO

4. Create user in Olap Server

5. problems with sql statement using access 2000, ado, vb6

6. How does my new algorithm show up in the SQL SERVER Analysis Manager

7. Problem opening recordset for update ADO 2.1/Jet 4.0/Access 2000 stored proc

8. Book Review? - stored procedures

9. Access 2000 and ADO 2.1 too few parameters error on insert

10. Problem opening recordset for update ADO 2.1/Jet 4.0/Access 2000 stored proc

11. ADO 2, OLE-DB for ODBC, SQL 6.5, VB6: Performance Issues

12. Modify Access 2000 table structure using ADO in VB6

13. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax