Saving notepad file into a database (Microsoft Access)

Question

How do i import a Notepad file into VB 6.0 and then save the content into a database (Microsoft Access database)

Answer 1

Basic steps (for any language/database):

1. Read the file

2. Connect to a database using a connection string

3. Insert into tables

Refer Connecting Access 2003 with Visual Basic 6 for specific information and code.

Answer 2

You might try to load the notepad file using Microsoft Excel and then try to get it into Microsft Acces from there.

Answer 3

I'm gonna read between the lines here and guess you are refering to some kind of tablular data held in a text file.

If this is the case then perhaps the Jet (or ODBC) connection strings found here (along with a schema.ini file) may be what you are looking for.

You can then use ADODB recordsets to select data from the text file and insert it into you Access database.

Answer 4

Okay, I'm doing this a lot lately. So, assuming you've got a reference set to SCRRUN.DLL, this first function will read the file (and will cope with ANSI and Unicode):

Function ReadFileE(Filename As Variant) As String
 Dim oStream As scripting.TextStream
 Dim sData As String

 Set oFSO = New scripting.FileSystemObject
 sData = vbNullString
 On Error Resume Next
 Set oStream = oFSO.OpenTextFile(Filename, ForReading, False, TristateMixed)
 If Err.Number = 0 Then
  sData = oStream.ReadAll
  oStream.Close
 Else
  sData = vbNullString
     End If
     On Error GoTo 0
     ReadFileE = sData
End Function

The following functions I use to open a database and query it:

Function OpenMDB(sFile As Variant) As Variant  ' ADODB.Connection
    Dim nErr As Long
    Dim sErr As String
    Dim oConnection As Object 'ADODB.Connection
    Set oConnection = CreateObject("ADODB.Connection")
    On Error Resume Next
    oConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFile
    nErr = Err.Number
    sErr = Err.Description
    On Error GoTo 0
    If nErr <> 0 Then
 Err.Raise OPENDATABASE_E_NOTFOUND, , sErr
    End If
    Set OpenMDB = oConnection
End Function

Function QueryMDB(ByRef oDB As Variant, sQuery As Variant, Optional bCmdText As Boolean = False) As Variant ''ADODB.Connection ADODB.Recordset
    Const adOpenForwardOnly  As Long = 0
    Const adOpenStatic As Long = 3
    Const adOpenDynamic As Long = 2
    Const adOpenKeyset As Long = 1
    Const adLockOptimistic As Long = 3
    Const adCmdText As Long = 1
    Dim oRecordSet As Object 'ADODB.Recordset
    Set oRecordSet = CreateObject("ADODB.RecordSet")
    oRecordSet.Open sQuery, oDB, adOpenKeyset, adLockOptimistic
    Set QueryMDB = oRecordSet
End Function

And now a bit of sample code:

Dim sText As String
sText = ReadFileE("c:\windows\system32\SQLSRDME.TXT")
Dim db As Variant
Dim rs as Variant
set db = OpenMDB("c:\users\bruce\documents\database.mdb")
set rs = QueryMDB( db, "SELECT * FROM Table1" )
rs.Find "Field1='SQLSRDME.TXT'"
If Not rs.EOF Then
    rs("Field2") = sText
    rs.Update
End If
rs.Close
db.Close

That last bit was typed off the top of my head, so it may have holes. The other code is out a Class file that is used daily.

Also, the class file code is exposed in a COM object, thus the preponderance of Variant declarations.

HTH.