We help IT Professionals succeed at work.

Need example of an Microsoft Access VBA conversion from ADO to VB.NET / ADO.NET using the following sample:

stephenlecomptejr
stephenlecomptejr asked
on
I'm trying to write a VB.NET program that does exactly what this Microsoft Access VBA program does.   Basically you give it a directory path as a string then do a recursive for all the files and record the modified date for each one.  If you've done a search on that folder already it allows you to skip and continue your search.

Was hoping someone could rewrite the following below code that utilizes VB.NET and ADO.NET instead of ADO and VBA...   The tables were originally linked to a SQL Server database... so I need code that writes not to an Access database but to SQL Server.  (Already provided an Access database a sample with the local tables - had already done a SQL Server Upsizing Wizard to SQL)

As far as research on how to do this...   The problem with self - help books or the web is that they try to get you started with a read-only data set  and I need something that can write to the data.  Then most are old and change what the connect term was between 2.0 and 3.0 - then throw in a whole new way to connect that's read-only like LINQ (sigh!).......


Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

  Dim start_time As Date
  Dim end_time As Date
  
  start_time = Now()
  Call Seek_Files("*.*", "C:\temp\", True)
  end_time = Now()
  
  MsgBox ("Finished seeking files with seconds time: " & DateDiff("s", start_time, end_time) & ".")
  
End Sub

Private Sub Seek_Files(sWhat As String, sUsing As String, bRecursive As Boolean)
On Error GoTo Exit_This

  Dim fs As Object
  Dim dDirs As Object
  Dim dDir As Object
  Dim fFile As Object
  Dim objFile As Object
  Dim sSQL As String
  Dim sFile As String
  Dim bRec As Boolean
  Dim bExists As Boolean
  Dim sFileModified As String
  Dim sFileOrgModified As String
  Dim rs As DAO.Recordset
  
  sSQL = "SELECT * FROM [tblPaths] WHERE [PathName] = '" & sUsing & "' AND [PathComplete] = True"
  'Set rs = Currentdb.OpenRecordset(sSQL)   use if you want to use the local tables instead
  '- either rename below or delete and rename the local tables to the sql table names
  Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot, dbSeeChanges)
  If Not rs.EOF Then bExists = True Else bExists = False
  rs.Close
  Set rs = Nothing
  
  If bExists = False Then
    sSQL = "INSERT INTO [tblPaths] (PathName) VALUES ('" & sUsing & "')"
    CurrentDb.Execute sSQL
    DoEvents
  End If
  
  If bExists = True Then GoTo Exit_This
  
  bRec = bRecursive
  
  Set fs = CreateObject("Scripting.FileSystemObject")
  Set dDirs = fs.GetFolder(sUsing)
  
  For Each fFile In dDirs.Files
    'match filename to pdf extension
    
    bExists = False
    
    sFile = fFile
   
    If sFile Like sWhat Then
    
      DoEvents
      
      Debug.Print sFile
    
      Set objFile = fs.GetFile(sFile)
      sFileModified = objFile.DateLastModified
      Set objFile = Nothing

      'copy file to proper location...
    
      sSQL = "SELECT [FileName], [FileModified] FROM tblFiles WHERE [FileName] = '" & sFile & "'"
      'Set rs = Currentdb.OpenRecordset(sSQL)   use if you want to use the local tables instead
      '- either rename below or delete and rename the local tables to the sql table names
      Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot, dbSeeChanges)
      If Not rs.EOF Then
        sFileOrgModified = Nz(rs.Fields(1), "")
        bExists = True
      Else
        bExists = False
      End If
      rs.Close
      Set rs = Nothing
      
      If bExists = False Then
        sSQL = "INSERT INTO [tblFiles] (FileName, FileModified) VALUES ('" & sFile & "', #" & sFileModified & "#" & ")"
        CurrentDb.Execute sSQL
        DoEvents
      Else
        If sFileOrgModified <> sFileModified Then
          sSQL = "UPDATE [tblFiles] SET [FileModified] = '" & sFile & "' WHERE [FileName] = '" & sFile & "'"
          CurrentDb.Execute sSQL
          DoEvents
        End If
      End If
      
    End If
    
    If bRec = True Then
      For Each dDir In dDirs.SubFolders
        Call Seek_Files(sWhat, dDir.Path, bRec)  ' Here is the recursion
      Next
    End If
    
  Next
  
  sSQL = "UPDATE [tblPaths] SET [PathComplete] = True WHERE [PathName] = '" & sUsing & "'"
  CurrentDb.Execute sSQL
  DoEvents

Exit_This:
  Exit Sub

Err_This:
  Resume Exit_This
End Sub

Open in new window

FileRecorder.zip
Comment
Watch Question

I think you are on the wrong site for such a request.
We come here to answer technical Qs.
This is just a request for work to be done.

Others may have a different view of course.

Author

Commented:
I just need the rewrite that incorporates ADO.NET instead of DAO and ADO.
Some have been willing to do so in the past and those are the comments I'm asking for.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot, dbSeeChanges)
  If Not rs.EOF Then bExists = True Else bExists = False
  rs.Close
  Set rs = Nothing

Open in new window

Author

Commented:
Same with

CurrentDb.Execute sSQL
    DoEvents

If I could have the one code to do those above statements - obviously I could rewrite the whole code.

Please note I know the following would be the way to start it in VB.net
Public Class frmMain

  
  Private Sub frmMain_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown

    Dim start_time As Date
    Dim end_time As Date
   
    start_time = Now()
    Call Seek_Files("*.*", "C:\temp\", True)
    end_time = Now()
  
    MsgBox ("Finished seeking files with seconds time: " & DateDiff("s", start_time, end_time) & ".")

  End Sub

  Private Sub Seek_Files(sWhat As String, sUsing As String, bRecursive As Boolean

Open in new window

Author

Commented:
The above link won't work for me as is similiar to a lot of books out there because they use a wizard instead of using actual code.   Then when it goes into a code - none of it shows how I can execute a sql statement in a string as above.  

Thanks though peter.  I'm waiting for someone willing to give me some sample code that specifically meets my needs above not another tutorial.
NorieAnalyst Assistant

Commented:
The code you posted doesn't use ADO or VBA, as far as I can see it's DAO and VB(6?).

What exactly does the code do anyway?

You mention something to do with files but not where a database comes into it.

Author

Commented:
This was done with Access VBA - but it could be used in VB(6).  You are correct I have it stated wrong as ADO and it is DAO...but I was in the midst of editing the posting originally but could not after the first expert comment.

What it does is described in the first paragraph and if you have Access you can download the file sample above to try.
Most Valuable Expert 2012
Top Expert 2014
Commented:
I will try to get you started. With the code in http:#37234264 it looks like you are trying to find out if a record exists. Try following in ADO.NET


Dim dbcon As New OleDbConnection("your connection string")
dbcon.Open()
Dim dbcmd As New OleDbCommand
dbcmd.Connection = dbcon
dbcmd.CommandText = "SELECT Count(*) FROM [tblPaths] WHERE [PathName] = '" & sUsing & "' AND [PathComplete] = True"

Dim count As Integer = dbcmd.ExecuteScalar()

If count > 0 Then
   'exists
End If





For the insert:
dbcmd.CommandText = "Insert Into ..."
dbcmd.ExecuteNonQuery()

Author

Commented:
This is all I ask for CodeCruiser - very appreciative of this.  Will try tomorrow on it.
Thanks again.

Stephen

Author

Commented:
The only issue I'm having now is converting the DAO.Recordset Loop.  The following code is what I have thus far:  But I get an error - Variable 'myCommand' is used before it has been assigned a value. A null reference exception could result at runtime.  with the highlight on  myCommand.CommandText = sSQL.  

I tried to incorporate what was stated at this website but not sure the correct syntax to create an instance of the Command object.

http://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.71).aspx
sSQL = "SELECT [FileName], [FileModified] FROM tblFiles WHERE [FileName] = '" & sFile & "'"
                Dim myCommand As SqlCommand
                myCommand.CommandText = sSQL
                Dim myReader As SqlDataReader = myCommand.ExecuteReader()
                sFileOrgModified = ""
                If myReader.HasRows Then
                    Do While myReader.Read()

                        sFileOrgModified = myReader.GetString(1)
                        bExists = True
                    Loop
                Else
                    bExists = False
                End If
                myReader.Close()

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Change this
Dim myCommand As SqlCommand

to

Dim myCommand As New SqlCommand

You also need to create and assign a SqlConnection object to the command.Connection property.

Author

Commented:
That has helped me a lot- all I have is my SQL statement now to fix.
I will post it another question.

Thank you so much Code Cruiser!!!
I appreciate it.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Glad to help :-)