String Provider Error Problems...

[Bill Foley]

Sorry if this is the wrong group, but it is the first time I have a need to
modify my code so I am trying you all for assistance:

I have a PowerPoint CBT that uses VBA to write data to an Access database.
Unfortunately our company has a variety of OS's and Access (either Win NT
4.0 and Access 97 or Windows 2000 and Access XP). I have some code that
uses either Jet 4.0 or 3.51. What I have found is that a majority of users
have 4.0 so I have error code that captures 3.51 and sends a different
message to me. What I was hoping for is a way to make both versions able to
access the database.

Basically it is set up that those who have 4.0 can write to the database but
those who have 3.51 can't. I get an e-mail showing the "DatabaseStatus".
If it says "You may need to update the database", then I manually enter the
data. I am sure there is an easy way to allow either provider type to
access the database.

FYI - I created the database in Access XP but converted it back to an Access
97 since both versions can open it to write data to it. Any assistance you
could provide would be greatly appreciated. I have contemplated referencing
the DAO directly or possibly coming up with an IF statement that does the
writing part, but my talents in Access (and VBA) are somewhat limited.

Here is the code:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.RecordSet
Dim strDataSource As String
Dim strProvider As String
Dim strRecordSource As String

On Error GoTo Errorhandler

' Setup data source, provider, and table
strDataSource = "Data Source=\\myserverpathhere\mymdbhere.mdb"
' This line used for newer Access provider files
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
strDatabaseStatus = "Updated"

GoTo Complete

Errorhandler:

' This line used for other Access provider files
strProvider = "Provider=Microsoft.Jet.OLEDB.3.51;"
strDatabaseStatus = "You may need to update the database"
GoTo Continue
Complete:

strRecordSource = "TrainingRecord"

'Open connection
cnn.Open strProvider & strDataSource

'Open recordset
rst.Open strRecordSource, cnn, adOpenDynamic, adLockOptimistic

'add record and fill fields from data array
rst.AddNew
rst!SSN = strSSN
rst!firstname = strfirstname
rst!lastname = strlastname
rst!Feedback1 = strFeedback1
rst!Feedback2 = strFeedback2
rst!Feedback3 = strFeedback3
rst!Feedback4 = strFeedback4
rst!matcode = strMatCode
rst!CBTtitle = strCBTTitle
rst!Score = Score
rst!DateTaken = Now
rst.Update
rst.Close

Thanks in advance!

Bill

 

[PTT, Inc.]

Never mind. I simply duplicated the Add Record steps to each section and
included GoTo statements to make either version of Office work. Kind of a
stone and chisel method but it worked!

Bill