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

    export tables to sql server using vba

    I have 23 tables I have to upload to a SQL Server development environment. We have encountered a lot of bugs in the migration, so I end up having to do this repeatedly till we get them ironed out. I know how to export one table at a time manually, but I would


    like to do this using VBA to make the chore easier.
    This is my file DSN I use to connect to SQL


    Code:
    
    [ODBC]
    
    DRIVER=SQL Server
    
    DATABASE=MortalityDev
    
    WSID=ABC0010866
    
    APP=Microsoft Office 2016
    
    Trusted_Connection=Yes
    
    SERVER=xxxxxxx,50000
    I haven't been able to translate any examples I've found online into workable code.

    Thanks in advance.

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Can you describe in more detail the problems that you're having?

    Are you not able to establish a connection to the server? (FYI https://www.connectionstrings.com/sql-server/ )

    Have you tried importing data using SSMS rather than access vba?

    From access vba, do simple INSERT INTO SELECT statements not work?

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    can you just run an append query?

  4. #4
    Paul H's Avatar
    Paul H is offline Expert
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    I could, but I'm as much as the table structure as I am with the data. I expected the migration to go smoothly, but we have had one problem after another. The Access db is not able to modify any new records entered into SQL Server. It works just fine on records entered before the migration. That's the actual problem we are trying to solve. If we can figure out what is causing that, I wouldn't have to do anymore uploading.

  5. #5
    Paul H's Avatar
    Paul H is offline Expert
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    I reloaded the tables manually and ran all my scripts again and for some strange reason I can add and update now. The last five attempts all failed.
    I am shutting down till Monday. I'll give you an update then.

    I thank you for your replies.

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

Similar Threads

  1. Export Query To SQL Server
    By Juan4412 in forum Programming
    Replies: 7
    Last Post: 05-24-2017, 11:49 AM
  2. export to a Server...instead of excel to dropbox!@
    By gangel in forum Import/Export Data
    Replies: 1
    Last Post: 03-29-2016, 08:50 AM
  3. Export Access 2013 to sql server 2012
    By jassie in forum Import/Export Data
    Replies: 2
    Last Post: 05-18-2015, 01:03 PM
  4. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  5. Replies: 4
    Last Post: 11-22-2013, 11:20 AM

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