mdb to accdb conversion

[Knowlton]

I have a DB that was created in Access 2000. I remember seeing that there were issues with 2007 when it came out so I never upgraded to it. We're now using an Office 365 subscription. Lately, occasionally, the DB will stop working with an error message "can't find the file". When I try to open the data file there is an error message that says the file has become unstable and Access will try to recover it. This has worked so far but I think I need to convert to the current format. My problem is that I do not have any of the previous versions that will allow the conversion. Does anyone have any suggestions?

Thanks!

[twinnyfo]

Knowlton,

As far as I know, you "should" be able to create a new DB in Access 365, then import all your objects from your old DB into your new DB. In my experience, this is the "best" way to have a completely fresh and updated DB. Don't forget to update any references that you used in your VBA editor.

There still may be a few compatibility issues, but most of those should be relatively few, and relatively easy to address, based upon any error messages you receive.

Hope this hepps.

If you come across any snags, please let us know and we will address particulars.

[Knowlton]

Thanks for your reply! I have a split DB so I'm assuming that I need to convert both front end and back end files.

[twinnyfo]

Yes. Then just run the Linked Table Manager to relink to the new BE.

Best of luck!

[Knowlton]

Thanks! I'll probably need it.

[Knowlton]

I'm having trouble trying to correct the following issue. I have an "Invalid use of NEW keyword" error message on the line "Dim ListStates As New Recordset" in the following code:

Private Sub State_NotInList(NewData As String, Response As Integer)
    'Provide for adding a new state
    Dim Confirm
    Confirm = MsgBox(StrConv(NewData, vbProperCase) _
        & " Not In List." & _
        vbCrLf & "Do You Want To Add It?", _
        vbInformation + vbYesNo, "Limited Entry")
    If Confirm = vbYes Then
        Dim ListStates As New Recordset
        ListStates.Open Source:="tlkpStates", _
            ActiveConnection:=CurrentProject.Connection, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockOptimistic
        ListStates.AddNew
        ListStates!State = NewData
        ListStates.Update
        Me.State = NewData
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
 
End Sub

Thanks.

[twinnyfo]

Try declaring it directly as an ADODB Recordset, as this is what it is (apparently).

Dim ListStates As ADODB.Recordset

[Knowlton]

Get a compile error "user-defined type not defined"

[twinnyfo]

You’ll have to add the reference to ADO.

[Knowlton]

I'm not sure how to do this. I've been unable to get anything to work.

[twinnyfo]

In your VBA Editor, Tools, References....

[Knowlton]

Microsoft Office 16.0 Access database engine Object Library is checked. That's the only thing I see relative to ADO.

[twinnyfo]

Microsoft ActiveX Data Objects 6.1 Library

Hope that hepps!