Results 1 to 9 of 9
  1. #1
    Paul H's Avatar
    Paul H is offline Expert
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    566

    Trouble exporting to SQL Server

    I've done this before several times, but now I'm getting different results. Exports before used to go direct to the dbo schema. Now they are going to my personal schema MyOrganization\MyID.tblName.



    I have also tried using the Import Data feature in SQL Server. I got close once. now I'm at a dead end. I'm using Access 2016 and SSMS 2019

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,795
    I use a DSN that points to the SQL server.
    it reads/writes just fine. Even when I swap out the server from production to testing.

  3. #3
    Paul H's Avatar
    Paul H is offline Expert
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    566
    I do that all the time. What I am trying to do is export local Access tables to a SQL Server,

  4. #4
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    723
    Looks like the default schema for your user group has been changed. Check in SSMS - security -users the default schema for the database.

  5. #5
    wvmitchell is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    18
    It is possible that you previously had dbo rights, but they were removed?

  6. #6
    Paul H's Avatar
    Paul H is offline Expert
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    566
    FYI, I should have marked this thread solved. My local dba explained it to me.

    Thanks

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,688
    Posting the reason(s) could help someone with the same problem later.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Paul H's Avatar
    Paul H is offline Expert
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    566
    I wish I could help, but this thread being 3 months old, I don't remember the details. My dba helped me write a script to change the schema to dbo. The problem has not reoccurred since.

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,688
    Sorry, I didn't notice that. I have trouble remembering what I had for lunch yesterday, so I understand.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Upgrade from SQL Server 2008 to SQL Server 2014
    By RayMilhon in forum SQL Server
    Replies: 4
    Last Post: 07-12-2017, 04:54 PM
  2. Trouble exporting to file and preserving word wrap
    By Gary Childress in forum Import/Export Data
    Replies: 13
    Last Post: 09-30-2015, 03:49 PM
  3. Replies: 11
    Last Post: 02-26-2015, 11:00 AM
  4. Replies: 1
    Last Post: 08-17-2012, 04:50 PM
  5. Exporting to HTML Trouble
    By starhawk85 in forum Import/Export Data
    Replies: 2
    Last Post: 05-03-2012, 06:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums