Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-17-2018, 01:53 AM   #1
Derek
Newly Registered User
 
Join Date: May 2010
Posts: 160
Thanks: 13
Thanked 0 Times in 0 Posts
Derek is on a distinguished road
Data Migration From Access to sql server

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

Derek is offline   Reply With Quote
Old 05-17-2018, 02:04 AM   #2
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,963
Thanks: 54
Thanked 1,920 Times in 1,834 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Data Migration From Access to sql server

Rename them all and remove the dbo.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 05-17-2018, 02:04 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,184
Thanks: 123
Thanked 1,416 Times in 1,388 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Data Migration From Access to sql server

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.

__________________
If we have helped;
Please click the 'reputation' scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 05-17-2018, 02:05 AM   #4
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,857
Thanks: 73
Thanked 1,199 Times in 1,120 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Data Migration From Access to sql server

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
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 05-17-2018, 02:06 AM   #5
Derek
Newly Registered User
 
Join Date: May 2010
Posts: 160
Thanks: 13
Thanked 0 Times in 0 Posts
Derek is on a distinguished road
Re: Data Migration From Access to sql server

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 is offline   Reply With Quote
Old 05-17-2018, 02:24 AM   #6
Derek
Newly Registered User
 
Join Date: May 2010
Posts: 160
Thanks: 13
Thanked 0 Times in 0 Posts
Derek is on a distinguished road
Re: Data Migration From Access to sql server

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")
Derek is offline   Reply With Quote
Old 05-17-2018, 02:27 AM   #7
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,857
Thanks: 73
Thanked 1,199 Times in 1,120 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Data Migration From Access to sql server

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

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

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by ridders; 05-17-2018 at 02:34 AM. Reason: Added info
ridders is offline   Reply With Quote
Old 05-17-2018, 02:28 AM   #8
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,963
Thanks: 54
Thanked 1,920 Times in 1,834 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Data Migration From Access to sql server

Then add it

Set rs = CurrentDb.OpenRecordset("tblAbsence, dbSeeGhanges)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 05-17-2018, 02:39 AM   #9
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,184
Thanks: 123
Thanked 1,416 Times in 1,388 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Data Migration From Access to sql server

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...
__________________
If we have helped;
Please click the 'reputation' scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 05-17-2018, 03:39 AM   #10
Derek
Newly Registered User
 
Join Date: May 2010
Posts: 160
Thanks: 13
Thanked 0 Times in 0 Posts
Derek is on a distinguished road
Re: Data Migration From Access to sql server

Thank you very much guys. it worked a treat
Derek is offline   Reply With Quote
Old 05-18-2018, 01:23 AM   #11
Derek
Newly Registered User
 
Join Date: May 2010
Posts: 160
Thanks: 13
Thanked 0 Times in 0 Posts
Derek is on a distinguished road
Re: Data Migration From Access to sql server

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
    gFormRecordRef = rs!ActionPlanID
    rs.Update
    rs.Close
End Sub
Derek is offline   Reply With Quote
Old 05-18-2018, 01:58 AM   #12
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,184
Thanks: 123
Thanked 1,416 Times in 1,388 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Data Migration From Access to sql server

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
    rs.Update
    gFormRecordRef = rs!ActionPlanID
    rs.Close
End Sub

__________________
If we have helped;
Please click the 'reputation' scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access frontend data not updated into SQL server even after migration manusri SQL Server 3 05-21-2014 04:51 AM
Access to SQL Server Migration Assistant - SQL Server 2000 The Brown Growler SQL Server 5 10-18-2009 04:06 PM
Migration from MS-Access to MSSQL 2005 Server using VB 6 mynameiskamath Visual Basic 4 09-30-2009 10:15 PM
Access migration to SQL server issue Mikusss General 1 11-02-2007 04:39 AM
SQL Server Migration Assistant for Access shadow9449 General 4 07-25-2007 02:18 PM




All times are GMT -8. The time now is 04:25 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World