Data Migration From Access to sql server (1 Viewer)

Derek · May 17, 2018

Hi Guys

I am using upsizing wizard for data migration. I tried to migrate tables from both Access backend to sql server and it worked a treat. After the tables got exported in sql server I linked those tables to Access frontend .

But all the tables linked are starting with 'dbo.' So my Access forms don't work because they were earlier linked to tables named e.g 'tblStaff' but now that table is changed to 'dbo.tblStaff'.

How can I resolve this issue so that I don't have to change my forms and queries etc..?

Many Thanks

arnelgp · May 17, 2018

Rename them all and remove the dbo.

Minty · May 17, 2018

2 methods - manually remove the dbo_ by renaming.
Or if there are a lot of tables make a small VBA function to do it for you.

isladogs · May 17, 2018

Depending on how you setup SQL Server, that will happen.
At this stage the easiest solution is to use an alias for all tables without the dbo.

If you use connection strings rather than ODBC to link the tables, you can build that into the design

Derek · May 17, 2018

Thanks, I have found this code:

Code:

Public Sub subChangeLinkedTableNames()

    Dim dbCurr As DAO.Database
    Dim tdfCurr As DAO.TableDef

    Set dbCurr = CurrentDb()

    For Each tdfCurr In dbCurr.TableDefs
        If Len(tdfCurr.Connect) > 0 Then
            If Left(tdfCurr.Name, 4) = "dbo_" Then
                tdfCurr.Name = Replace(tdfCurr.Name, "dbo_", "")
            End If
        End If
    Next


    Set tdfCurr = Nothing
    Set dbCurr = Nothing

End Sub

Derek · May 17, 2018

Another issue guys, when i try to run forms then getting runtime error 3622 "You must use the dbseechanges option with openrecordset ... in the following line of code:

Code:

Set rs = CurrentDb.OpenRecordset("tblAbsence")

isladogs · May 17, 2018

Yes, you have to use that with all recordsets based on sql tables

Code:

Set rs = CurrentDb.OpenRecordset("tblAbsence", dbOpenDynaset, dbSeeChanges)

arnelgp · May 17, 2018

Then add it

Set rs = CurrentDb.OpenRecordset("tblAbsence, dbSeeGhanges)

Minty · May 17, 2018

Yes you will, You need to tell SQL server what type of recordset you are opening.
If it's read only you can use dbOpenSnapShot
If you want to make changes then normally you will need to use

Code:

Set rs = CurrentDb.OpenRecordset("tblAbsence",dbOpenDynaset, dbSeeChanges)

Edit - a bit slow fingered there...

Derek · May 17, 2018

Thank you very much guys. it worked a treat

Derek · May 18, 2018

Guys , I have another issue when adding a new record in a table. Actually the following code works fine when we have MS access backend as ActionPlanID is 'Autonumber' in MS access but when migrated to sql server the datatype is changed to 'int' and the following highlighted line of code gives a 'Runtime error 94, Invalid use of null'. How can I fix that?

Code:

Private Sub CreateRecord()
Set rs = CurrentDb.OpenRecordset(aTable, dbOpenDynaset, dbSeeChanges)
    rs.AddNew
    rs!ImplementedByRef = aUser
    rs!LastUpdated = Date
    rs!LastUpdatedByRef = aUser
    rs!StaffMemberRef = gTargetStaffRef
   [COLOR="Red"] gFormRecordRef = rs!ActionPlanID[/COLOR]
    rs.Update
    rs.Close
End Sub

Minty · May 18, 2018

The id isn't created until it's saved so move your offending line after the update.

Code:

Private Sub CreateRecord()
Set rs = CurrentDb.OpenRecordset(aTable, dbOpenDynaset, dbSeeChanges)
    rs.AddNew
    rs!ImplementedByRef = aUser
    rs!LastUpdated = Date
    rs!LastUpdatedByRef = aUser
    rs!StaffMemberRef = gTargetStaffRef
 [COLOR="Red"]   rs.Update
    gFormRecordRef = rs!ActionPlanID[/COLOR]
    rs.Close
End Sub

Data Migration From Access to sql server (1 Viewer)

Derek

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Minty

AWF VIP

isladogs

MVP / VIP

Derek

Registered User.

Derek

Registered User.

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

Minty

AWF VIP

Derek

Registered User.

Derek

Registered User.

Minty

AWF VIP

Similar threads

Users who are viewing this thread