p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

activex_data_objects thread: need help on a code that deals with VB + ADOB.Connection + MessageDigest


Message #1 by "Leyla Yerlikaya" <leylay@h...> on Mon, 28 Jan 2002 19:49:59
Hi everybody,



First of all, I need to let you know that I am not a programmer, and I am 

in trouble with a VB program that I had to write.



I have created a VB document using MSWord. A small part of the code is as 

follows. This code basically, connects to a dbf file using 

ADODB.Connection, and creates encrypted columns (using messagedigest). The 

dataset I am working on has approximately 400,000 records. Although the 

code runs correctly, but it takes 2 minutes to effect 20 records, which 

means it will take app. 15 days to encrypt 400,000 records. I need to find 

a way to reduce this execution time to one hour. 



I will appreciate any kind information to solve my problem. I can send the 

entire code if it would be helpful. The following is only a small portion 

to provide you with some information. 



I am looking forward to hearing from you.



Thanks,



leyla



*********



' Add a field named column1 to the table

If CheckBox1.Value = True Then

  rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column1 & " CHAR

(16)", mcn

  TextBox1.Text = TextBox1.Text & "...New column " & Column1 & " is 

added..."

  If CheckBox2.Value = True Then

    MsgBox ("Added " & Column1 & " column")

  End If



End If



' Add a field named column2 to the table

If CheckBox1.Value = True Then

 rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column2 & " CHAR

(16)", mcn

 TextBox1.Text = TextBox1.Text & "...New column " & Column2 & " is 

added..."

 If CheckBox2.Value = True Then

    MsgBox ("Added " & Column2 & " column")

 End If

End If

' Get the info from table (SELECT)

   rs.Open "SELECT id_number, first_name, mid_name, last_name, post_name, 

line1, line2, city, state, " _

   & Column1 & ", " & Column2 & _

   " FROM " & FileName & " ", mcn

   

   TextBox1.Text = "SQL Query Select is executed..."

   If CheckBox2.Value = True Then

    MsgBox ("executed SQL Select")

   End If



   Report = TextBox1.Text

   

   Do While loop_var

         

         loop_var = Not rs.EOF

         numberofrecs = numberofrecs + 1

            

         ' Calculate the first hash

         rs.Fields(Column1).Value = encoder.CRC(UCase(Trim(rs.Fields

("first_name"))) & " " _

         & UCase(Trim(rs.Fields("mid_name"))) & " " & UCase(Trim(rs.Fields

("last_name"))) _

         & " " & UCase(Trim(rs.Fields("post_name"))))

           

         ' Calculate the second hash

         rs.Fields(Column2).Value = encoder.CRC(UCase(Trim(rs.Fields

("line1"))) _

         & " " & UCase(Trim(rs.Fields("line2"))) & " " & UCase(Trim

(rs.Fields("city"))) _

         & " " & UCase(Trim(rs.Fields("state"))))

           

       
Message #2 by "David Adams" <davidadams00@h...> on Mon, 28 Jan 2002 15:05:36 -0500
Just a few questions:

1) I assume Encoder is a separate class or dll.

2) Is the ALTER Table a requirement?  If so, is it a requirement that you do

it multiple times?



These are just two things that jumped out at me as possible areas to focus

attempts to improve performance.  In either the case of the Class or DLL for

Encoder or the ALTER TABLE statement, your best case is to minimize the

iterations that you do similar operations (especially if the process is

accomplished over a non-local connection).



Hope this gives you a few ideas of where to start.



David Adams

----- Original Message -----

From: "Leyla Yerlikaya" <leylay@h...>

To: "ActiveX_Data_Objects" <activex_data_objects@p...>

Sent: Monday, January 28, 2002 7:49 PM

Subject: [activex_data_objects] need help on a code that deals with VB +

ADOB.Connection + MessageDigest





> Hi everybody,

>

> First of all, I need to let you know that I am not a programmer, and I am

> in trouble with a VB program that I had to write.

>

> I have created a VB document using MSWord. A small part of the code is as

> follows. This code basically, connects to a dbf file using

> ADODB.Connection, and creates encrypted columns (using messagedigest). The

> dataset I am working on has approximately 400,000 records. Although the

> code runs correctly, but it takes 2 minutes to effect 20 records, which

> means it will take app. 15 days to encrypt 400,000 records. I need to find

> a way to reduce this execution time to one hour.

>

> I will appreciate any kind information to solve my problem. I can send the

> entire code if it would be helpful. The following is only a small portion

> to provide you with some information.

>

> I am looking forward to hearing from you.

>

> Thanks,

>

> leyla

>

> *********

>

> ' Add a field named column1 to the table

> If CheckBox1.Value = True Then

>   rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column1 & " CHAR

> (16)", mcn

>   TextBox1.Text = TextBox1.Text & "...New column " & Column1 & " is

> added..."

>   If CheckBox2.Value = True Then

>     MsgBox ("Added " & Column1 & " column")

>   End If

>

> End If

>

> ' Add a field named column2 to the table

> If CheckBox1.Value = True Then

>  rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column2 & " CHAR

> (16)", mcn

>  TextBox1.Text = TextBox1.Text & "...New column " & Column2 & " is

> added..."

>  If CheckBox2.Value = True Then

>     MsgBox ("Added " & Column2 & " column")

>  End If

> End If

> ' Get the info from table (SELECT)

>    rs.Open "SELECT id_number, first_name, mid_name, last_name, post_name,

> line1, line2, city, state, " _

>    & Column1 & ", " & Column2 & _

>    " FROM " & FileName & " ", mcn

>

>    TextBox1.Text = "SQL Query Select is executed..."

>    If CheckBox2.Value = True Then

>     MsgBox ("executed SQL Select")

>    End If

>

>    Report = TextBox1.Text

>

>    Do While loop_var

>

>          loop_var = Not rs.EOF

>          numberofrecs = numberofrecs + 1

>

>          ' Calculate the first hash

>          rs.Fields(Column1).Value = encoder.CRC(UCase(Trim(rs.Fields

> ("first_name"))) & " " _

>          & UCase(Trim(rs.Fields("mid_name"))) & " " & UCase(Trim(rs.Fields

> ("last_name"))) _

>          & " " & UCase(Trim(rs.Fields("post_name"))))

>

>          ' Calculate the second hash

>          rs.Fields(Column2).Value = encoder.CRC(UCase(Trim(rs.Fields

> ("line1"))) _

>          & " " & UCase(Trim(rs.Fields("line2"))) & " " & UCase(Trim

> (rs.Fields("city"))) _

>          & " " & UCase(Trim(rs.Fields("state"))))

>

>




$subst('Email.Unsub').

>

Message #3 by "Leyla Yerlikaya" <leylay@h...> on Mon, 28 Jan 2002 20:22:49
<html><div style='background-color:'><DIV>

<P>David,</P>

<P>I think the problem is starting after ALTER TABLE statement. The encoder is stamin.dll. If you don't mind I am sending the
code to you. I am not running a database, I am connecting to a database using ODBC driver.</P>

<P>I will appreciate any kind of information on this matter. </P>

<P>Thanks,</P>

<P>leyla</P>

<P>******************</P>

<P>Option Compare Database<BR>Option Explicit</P>

<P>Option Explicit<BR>Public mcn As New ADODB.Connection<BR>Public rs As New ADODB.Recordset<BR>Public
connstr As String</P>

<P>' Function to calculate the MD5<BR>Private Sub CreateKeys()</P>

<P>' ODBC Driver Name<BR>Dim Dir_File As String<BR>Dir_File = DirectoryBox.Text<BR>connstr =
"DRIVER=Microsoft Visual FoxPro
Driver;UID=;Deleted=Yes;Null=Yes;Collate=Machine;BackgroundFetch=Yes;Exclusive=yes;SourceType=DBF;SourceDB=" &amp; Dir_File
&amp; " "</P>

<P>mcn.Open connstr<BR>mcn.CommandTimeout = 0</P>

<P>Dim rs As New Recordset<BR>Dim encoder As New MessageDigest.Stamina<BR>Dim rec As Long<BR>Dim upd As
Long<BR>Dim numberofrecs As Long<BR>Dim FileName As String<BR>Dim Column1 As String<BR>Dim Column2 As
String<BR>Dim Report As String<BR>Dim loop_var As Boolean</P>

<P>'rs.CursorType = adOpenDynamic</P>

<P>loop_var = True</P>

<P>If BatchBox.Value = True Then<BR>&nbsp;&nbsp; mcn.CursorLocation = adUseClient<BR>&nbsp;&nbsp;
rs.LockType = adLockBatchOptimistic<BR>&nbsp;&nbsp; rs.CursorType =
adOpenStatic<BR>Else<BR>&nbsp;&nbsp; mcn.CursorLocation = adUseServer<BR>&nbsp;&nbsp; rs.LockType
= adLockOptimistic<BR>&nbsp;&nbsp; rs.CursorType = adOpenForwardOnly<BR>End
If<BR>&nbsp;&nbsp;&nbsp; <BR>' File name of the database file (DBASE)<BR>FileName =
FileNameBox.Text<BR>Column1 = Column1Box.Text<BR>Column2 = Column2Box.Text</P>

<P>numberofrecs = 0</P>

<P>' Initialize the text box<BR>TextBox1.Text = "Connection to database is established"</P>

<P><BR>' Add a field named column1 to the table<BR>If CheckBox1.Value = True Then<BR>&nbsp; rs.Open
"ALTER TABLE " &amp; FileName &amp; " ADD COLUMN " &amp; Column1 &amp; " CHAR(16)", mcn<BR>&nbsp;
TextBox1.Text = TextBox1.Text &amp; "...New column " &amp; Column1 &amp; " is added..."<BR>&nbsp; If
CheckBox2.Value = True Then<BR>&nbsp;&nbsp;&nbsp; MsgBox ("Added " &amp; Column1 &amp; "
column")<BR>&nbsp; End If</P>

<P>End If</P>

<P>' Add a field named column2 to the table<BR>If CheckBox1.Value = True Then<BR>&nbsp;rs.Open "ALTER TABLE "
&amp; FileName &amp; " ADD COLUMN " &amp; Column2 &amp; " CHAR(16)", mcn<BR>&nbsp;TextBox1.Text =
TextBox1.Text &amp; "...New column " &amp; Column2 &amp; " is added..."<BR>&nbsp;If CheckBox2.Value = True
Then<BR>&nbsp;&nbsp;&nbsp; MsgBox ("Added " &amp; Column2 &amp; " column")<BR>&nbsp;End
If<BR>End If<BR>&nbsp;<BR>' Get the info from table (SELECT)<BR>&nbsp;&nbsp; rs.Open "SELECT
id_number, first_name, mid_name, last_name, post_name, line1, line2, city, state, " _<BR>&nbsp;&nbsp; &amp;
Column1 &amp; ", " &amp; Column2 &amp; _<BR>&nbsp;&nbsp; " FROM " &amp; FileName &amp; " ",
mcn<BR>&nbsp;&nbsp; <BR>&nbsp;&nbsp; TextBox1.Text = "SQL Query Select is
executed..."<BR>&nbsp;&nbsp; If CheckBox2.Value = True Then<BR>&nbsp;&nbsp;&nbsp; MsgBox ("executed
SQL Select")<BR>&nbsp;&nbsp; End If</P>

<P>&nbsp;&nbsp; Report = TextBox1.Text<BR>&nbsp;&nbsp; <BR>&nbsp;&nbsp; Do While
loop_var<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; loop_var = Not
rs.EOF<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; numberofrecs = numberofrecs +
1<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' Calculate the first
hash<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.Fields(Column1).Value =
encoder.CRC(UCase(Trim(rs.Fields("first_name"))) &amp; " "
_<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp;
UCase(Trim(rs.Fields("mid_name"))) &amp; " " &amp; UCase(Trim(rs.Fields("last_name")))
_<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp; " " &amp;
UCase(Trim(rs.Fields("post_name"))))<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n
bsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' Calculate the second
hash<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rs.Fields(Column2).Value =
encoder.CRC(UCase(Trim(rs.Fields("line1")))
_<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp; " " &amp;
UCase(Trim(rs.Fields("line2"))) &amp; " " &amp; UCase(Trim(rs.Fields("city")))
_<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &amp; " " &amp;
UCase(Trim(rs.Fields("state"))))<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' Display the current record being
processed<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TextBox1.Text = Report &amp;
"..Number of records processed:" &amp; numberofrecs &amp; "
"<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' Advance to the next
record<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
rs.MoveNext<BR>&nbsp;&nbsp;&nbsp;
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If CheckBox2.Value = True
Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If (numberofrecs
Mod TextBox2.Value) = 0
Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs
p;&nbsp; MsgBox ("Updated record " &amp;
numberofrecs)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End
If<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If</P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' if batch then push the data in chunks to
the file...<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If BatchBox.Value = True
Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If
(numberofrecs Mod BatchTextBox.Value) = 0
Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs
p;&nbsp;
rs.UpdateBatch<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs
p;&nbsp;&nbsp; If CheckBox2.Value = True
Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs
p;&nbsp;&nbsp;&nbsp;&nbsp; MsgBox ("pushed " &amp; BatchTextBox.Value &amp; " record to
database")<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&a
mp;nbsp;&nbsp; End
If<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
TextBox1.Text = TextBox1.Text &amp; " " &amp; BatchTextBox.Value &amp; " records
pushed"<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End
If<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If</P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If StopBox.Value = True And
StopTextBox.Value = numberofrecs
Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
loop_var = False<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End
If<BR>&nbsp;&nbsp;&nbsp; Loop</P>

<P>' Push the changes, commit...<BR>If BatchBox.Value = True Then<BR>&nbsp;
rs.UpdateBatch<BR>Else<BR>&nbsp; rs.Update<BR>End If</P>

<P>' Close the record set<BR>rs.Close</P>

<P>' Close the connection<BR>mcn.Close</P>

<P>MsgBox (numberofrecs &amp; " rows in file " &amp; FileName &amp; " are affected...")</P>

<P>End Sub</P>

<P><BR>Private Sub BatchBox_Click()<BR>&nbsp;&nbsp;&nbsp; If BatchBox.Value = True
Then<BR>&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
BatchTextBox.Visible = True<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Label6.Visible =
True<BR>&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;
Else<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BatchTextBox.Visible =
False<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Label6.Visible =
False<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; End
If<BR>End Sub</P>

<P>Private Sub CheckBox1_Click()<BR>&nbsp;&nbsp;&nbsp; If CheckBox1.Value = True
Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Column1Box.Visible =
True<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Column2Box.Visible =
True<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Label3.Visible =
True<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Label4.Visible =
True<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;
Else<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Column1Box.Visible =
False<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Column2Box.Visible =
False<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Label3.Visible =
False<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Label4.Visible =
False<BR>&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; End If<BR>End Sub</P>

<P>Private Sub CheckBox2_Click()<BR>&nbsp;&nbsp;&nbsp; If CheckBox2.Value = True
Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Label1.Visible =
True<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TextBox2.Visible =
True<BR>&nbsp;&nbsp;&nbsp; Else<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Label1.Visible = False<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TextBox2.Visible =
False<BR>&nbsp; <BR>&nbsp;&nbsp;&nbsp; End If<BR>End Sub</P>

<P>Private Sub CommandButton1_Click()<BR>&nbsp;&nbsp;&nbsp; ' In the event of button click call CreateKeys
function<BR>&nbsp;&nbsp;&nbsp; CreateKeys<BR>End Sub</P>

<P>&nbsp;</P>

<P>Private Sub Command0_Enter()</P>

<P>End Sub</P>

<P>Private Sub Form_Current()</P>

<P>End Sub<BR><BR></P></DIV></div><br clear=all><hr>Chat with friends online, try
MSN Messenger: <a href='http://go.msn.com/bql/hmtag1_etl_EN.asp'>Click Here</a><br></html>

Message #4 by "David Adams" <davidadams00@h...> on Mon, 28 Jan 2002 15:56:49 -0500
This is a multi-part message in MIME format.



------=_NextPart_000_007C_01C1A814.65143540

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Can't say that this will hurt or help.



From just a process flow.



You might try to process your ALTER TABLE statement with a 

Connection.Execute.  Instead of settting the Connection property of a 

Recordset and running your ALTER SQL against a Recordset.Open.  Perform 

it as a Connection.Execute.  Also, it might be more valuable to handle 

all of the ALTER TABLE commands as one Execute statement instead of 

multiple ones.



Basically, use your If-Then-Else structures to create the ALTER TABLE 

statements, then have only one Connection.Execute.  That assumes that 

you can do your ALTER statements as one process instead of more than 

one.  That might get you some speed.



As for the DLL, I wouldnt think that a local DLL would not cause that 

much problem.  However, you might want to do 1 transfer to the DLL 

instead of multiple communications to it.  If it is on a remote machine, 

reducing the multiple trips might help performance as well.



David Adams       

  ----- Original Message -----

  From: Leyla Yerlikaya

  To: ActiveX_Data_Objects

  Sent: Monday, January 28, 2002 8:22 PM

  Subject: [activex_data_objects] Re: need help on a code that deals 

with VB + ADOB.Connection + MessageDigest





  David,



  I think the problem is starting after ALTER TABLE statement. The 

encoder is stamin.dll. If you don't mind I am sending the code to you. I 

am not running a database, I am connecting to a database using ODBC 

driver.



  I will appreciate any kind of information on this matter.



  Thanks,



  leyla



  ******************



  Option Compare Database

  Option Explicit



  Option Explicit

  Public mcn As New ADODB.Connection

  Public rs As New ADODB.Recordset

  Public connstr As String



  ' Function to calculate the MD5

  Private Sub CreateKeys()



  ' ODBC Driver Name

  Dim Dir_File As String

  Dir_File =3D DirectoryBox.Text

  connstr =3D "DRIVER=3DMicrosoft Visual FoxPro 

Driver;UID=3D;Deleted=3DYes;Null=3DYes;Collate=3DMachine;BackgroundFetch=3D

Yes;Exclusive=3Dyes;SourceType=3DDBF;SourceDB=3D" & Dir_File & " "



  mcn.Open connstr

  mcn.CommandTimeout =3D 0



  Dim rs As New Recordset

  Dim encoder As New MessageDigest.Stamina

  Dim rec As Long

  Dim upd As Long

  Dim numberofrecs As Long

  Dim FileName As String

  Dim Column1 As String

  Dim Column2 As String

  Dim Report As String

  Dim loop_var As Boolean



  'rs.CursorType =3D adOpenDynamic



  loop_var =3D True



  If BatchBox.Value =3D True Then

     mcn.CursorLocation =3D adUseClient

     rs.LockType =3D adLockBatchOptimistic

     rs.CursorType =3D adOpenStatic

  Else

     mcn.CursorLocation =3D adUseServer

     rs.LockType =3D adLockOptimistic

     rs.CursorType =3D adOpenForwardOnly

  End If

     

  ' File name of the database file (DBASE)

  FileName =3D FileNameBox.Text

  Column1 =3D Column1Box.Text

  Column2 =3D Column2Box.Text



  numberofrecs =3D 0



  ' Initialize the text box

  TextBox1.Text =3D "Connection to database is established"





  ' Add a field named column1 to the table

  If CheckBox1.Value =3D True Then

    rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column1 & " 

CHAR(16)", mcn

    TextBox1.Text =3D TextBox1.Text & "...New column " & Column1 & " is 

added..."

    If CheckBox2.Value =3D True Then

      MsgBox ("Added " & Column1 & " column")

    End If



  End If



  ' Add a field named column2 to the table

  If CheckBox1.Value =3D True Then

   rs.Open "ALTER TABLE " & FileName & " ADD COLUMN " & Column2 & " 

CHAR(16)", mcn

   TextBox1.Text =3D TextBox1.Text & "...New column " & Column2 & " is 

added..."

   If CheckBox2.Value =3D True Then

      MsgBox ("Added " & Column2 & " column")

   End If

  End If

  

  ' Get the info from table (SELECT)

     rs.Open "SELECT id_number, first_name, mid_name, last_name, 

post_name, line1, line2, city, state, " _

     & Column1 & ", " & Column2 & _

     " FROM " & FileName & " ", mcn

    

     TextBox1.Text =3D "SQL Query Select is executed..."

     If CheckBox2.Value =3D True Then

      MsgBox ("executed SQL Select")

     End If



     Report =3D TextBox1.Text

    

     Do While loop_var

          

           loop_var =3D Not rs.EOF

           numberofrecs =3D numberofrecs + 1

             

           ' Calculate the first hash

           rs.Fields(Column1).Value =3D 

encoder.CRC(UCase(Trim(rs.Fields("first_name"))) & " " _

           & UCase(Trim(rs.Fields("mid_name"))) & " " & 

UCase(Trim(rs.Fields("last_name"))) _

           & " " & UCase(Trim(rs.Fields("post_name"))))

            

           ' Calculate the second hash

           rs.Fields(Column2).Value =3D 

encoder.CRC(UCase(Trim(rs.Fields("line1"))) _

           & " " & UCase(Trim(rs.Fields("line2"))) & " " & 

UCase(Trim(rs.Fields("city"))) _

           & " " & UCase(Trim(rs.Fields("state"))))

            

        

           ' Display the current record being processed

           TextBox1.Text =3D Report & "..Number of records processed:" & 

numberofrecs & " "

          

           ' Advance to the next record

           rs.MoveNext

     

           If CheckBox2.Value =3D True Then

             If (numberofrecs Mod TextBox2.Value) =3D 0 Then

                MsgBox ("Updated record " & numberofrecs)

             End If

           End If



           ' if batch then push the data in chunks to the file...

           If BatchBox.Value =3D True Then

              If (numberofrecs Mod BatchTextBox.Value) =3D 0 Then

                rs.UpdateBatch

                If CheckBox2.Value =3D True Then

                   MsgBox ("pushed " & BatchTextBox.Value & " record to 

database")

                End If

              TextBox1.Text =3D TextBox1.Text & " " & BatchTextBox.Value 

& " records pushed"

             End If

           End If



           If StopBox.Value =3D True And StopTextBox.Value =3D 

numberofrecs Then

              loop_var =3D False

           End If

      Loop



  ' Push the changes, commit...

  If BatchBox.Value =3D True Then

    rs.UpdateBatch

  Else

    rs.Update

  End If



  ' Close the record set

  rs.Close



  ' Close the connection

  mcn.Close



  MsgBox (numberofrecs & " rows in file " & FileName & " are 

affected...")



  End Sub





  Private Sub BatchBox_Click()

      If BatchBox.Value =3D True Then

     

          BatchTextBox.Visible =3D True

          Label6.Visible =3D True

     

      Else

          BatchTextBox.Visible =3D False

          Label6.Visible =3D False

         

      End If

  End Sub



  Private Sub CheckBox1_Click()

      If CheckBox1.Value =3D True Then

         Column1Box.Visible =3D True

         Column2Box.Visible =3D True

         Label3.Visible =3D True

         Label4.Visible =3D True

        

      Else

         Column1Box.Visible =3D False

         Column2Box.Visible =3D False

         Label3.Visible =3D False

         Label4.Visible =3D False

     

      End If

  End Sub



  Private Sub CheckBox2_Click()

      If CheckBox2.Value =3D True Then

         Label1.Visible =3D True

         TextBox2.Visible =3D True

      Else

         Label1.Visible =3D False

         TextBox2.Visible =3D False

   

      End If

  End Sub



  Private Sub CommandButton1_Click()

      ' In the event of button click call CreateKeys function

      CreateKeys

  End Sub







  Private Sub Command0_Enter()



  End Sub



  Private Sub Form_Current()



  End Sub











-------------------------------------------------------------------------

-----

  Chat with friends online, try MSN Messenger: Click Here




$subst('Email.Unsub').





  Return to Index