• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

how do I handle an Access 95 table through VB6 running on XP?

I am running XP Pro with Access 2000.  Access will allow me to append records through the table view, but I can't create any new form, report, query etc.  I do have VB6.  An Access expert suggested I use the MS Jet Engine.  I downloaded it, but didn't install it yet.  I am unfamiliar with the product.  Using debug -d I noticed that the Access file was created or modified at some point by Jet engine.  The file is used as an overlay to legacy mapping program called Falconview.
What I wish to do is create a form for end users to append map point data to this file, then be able to query and run reports against it.  This is urgently needed.
Thank you,
Russ
0
RUSS_EMI
Asked:
RUSS_EMI
  • 2
  • 2
3 Solutions
 
MYLimCommented:
'here are the code that i use to
'connect Access2000 database
'through Jet Engine

Dim Cnn As ADODB.Connection
Dim Rs As ADODB.Recordset
 
Private sub form_load()
On error goto ErrFound
Set Cnn = New ADODB.Connection
Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyProgram\Hprecorder\HpRecorder.mdb;User Id=admin;Password=;"

Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseClient
Rs.Open "Select distinct dono from invkeep group by dono order by dono asc", Cnn, adOpenStatic, adLockReadOnly

if Rs.eof = true then
 msgbox "Empty Recordset,Connection will be Close"
 rs.close
 set rs=  nothing

  Cnn.close
 set Cnn = nothing
 
 exit sub
end if

debug.print rs.fields(0).value & " "

 rs.close
 set rs=  nothing

  Cnn.close
 set Cnn = nothing
 
 exit sub
ErrFound:
msgbox "Err description : " & Err.Description
if rs.state=adstateOpen Then
  rs.close
  set rs = nothing
end if
 
if Cnn.state = adstateOpen then
  Cnn.close
  set Cnn = nothing
end if
end sub
0
 
MYLimCommented:
Beside that,Please :
1.Update VB6 Latest service Pack -> SP5
2.Update MDAC to latest verison -> MDAC2.8
3.Latest Jet Engine Service Pack ->8.0

0
 
RUSS_EMIAuthor Commented:
Thank you.  Does this code allow for appending, if not would you show a sample?
My table only consists of 4 items; ID, Latitude, Longitude and Description.
Russ
0
 
schmucjeCommented:
Here are two different ways you can append the records to the table

Private cnnAccess As ADODB.Connection
Private rstAccess As ADODB.Recordset
Private Const sAccessPassword As String = """"
Private Const sFilePath As String = ""somepath""
Private Const sFileSpec As String = ""db1.mdb""
Private Const sDataSource As String = sFilePath & sFileSpec
private Const sProvider As String = ""Microsoft.Jet.OLEDB.4.0""

Private Sub AppendBySQL()
 
  Set cnnAccess = New ADODB.Connection
  With cnnAccess
    .Provider = sProvider
    .Properties(""Jet OLEDB:Database Password"") = sAccessPassword
    .ConnectionString = sDataSource
    .Open
  End With

  cnnAccess.Execute "INSERT INTO tblMaps (id, lattitude, longitude, description) " & _
                              "VALUES " & _ txtId.Text & ", " & txtLat.Text & ", " & txtLon.Text & ", " & txtDesc.Text
 
  If Not cnnAccess Is Nothing Then
    If cnnAccess.State = adStateOpen Then
      cnnAccess.Close
    End If
    Set cnnAccess = Nothing
  End If

End Sub

Private Sub InsertByADORecordset()
 
  Set cnnAccess = New ADODB.Connection
  With cnnAccess
    .Provider = sProvider
    .Properties(""Jet OLEDB:Database Password"") = sAccessPassword
    .ConnectionString = sDataSource
    .Open
  End With

Set rstAccess = New ADODB.Recordset
  With rstAccess
    .LockType = adLockPessimistic
    .CursorLocation = adUseServer
    .CursorType = adOpenKeyset
    .ActiveConnection = cnnAccess
  End With

  cnnAccess.Execute "INSERT INTO tblMaps (id, lattitude, longitude, description) " & _
                              "VALUES " & _ txtId.Text & ", " & txtLat.Text & ", " & txtLon.Text & ", " & txtDesc.Text
 
  If Not cnnAccess Is Nothing Then
    If cnnAccess.State = adStateOpen Then
      cnnAccess.Close
    End If
    Set cnnAccess = Nothing
  End If

End Sub
0
 
schmucjeCommented:
I accidentally hit enter before finishing the InsertByADORecordset.

Private Sub InsertByADORecordset()
 
  Set cnnAccess = New ADODB.Connection
  With cnnAccess
    .Provider = sProvider
    .Properties(""Jet OLEDB:Database Password"") = sAccessPassword
    .ConnectionString = sDataSource
    .Open
  End With

  Set rstAccess = New ADODB.Recordset
  With rstAccess
    .LockType = adLockPessimistic
    .CursorLocation = adUseServer
    .CursorType = adOpenKeyset
    .ActiveConnection = cnnAccess
  End With

  rstAccess.AddNew
  rstAccess("id").value = txtId.Text
  rstAccess("lattitude").value = txtLat.Text
  rstAccess("longitude").value = txtLon.Text
  rstAccess("description").value = txtDesc.Text
  rstaccess.Update
   
  If Not rstAccess Is Nothing Then
    If rstAccess.State = adStateOpen Then
       rstAccess.Close
    End If
    Set rstAccess = Nothing
  End If
 
  If Not cnnAccess Is Nothing Then
    If cnnAccess.State = adStateOpen Then
      cnnAccess.Close
    End If
    Set cnnAccess = Nothing
  End If

End Sub

The checking of the ADO "states" before closing is a little overkill when it is within the same procedure as the open, but I usually have it in a seprate procedure.  I just copied and pasted here.

HIH,
joe
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now