Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45

    One table has a field "Job_File" that is a hyperlink. It appears that is not allowed?
    * Error 0xc0208265: Data Flow Task 1: Failed to retrieve long data for column "Job_File".
    (SQL Server Import and Export Wizard)

  2. #17
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Ah - I forgot about those - never used them...

    Hyperlink, Attachment, Lookup & Multivalued fields are all athema to the "Big boy" database backends.

    If you have a google I have seen some tricks to move a hyperlink field into a normal text field, and store the "URL" caption to a secondary field.
    This enables you to still use them with a bit of adjustment to the way they are displayed and opened..
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #18
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45
    It appears that the hyperlink was not the issue, "garbage" in 1 of the records was the culprit. We omitted that record from the import and all is good now.
    Thank you for your help!!

  4. #19
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Tip: if you migrate a database from Access to SQL server, this is a great moment to look at the design of your database and clean it up. It's also the right time to learn about the data types existing in SQL server. What works for me:
    step 1: analyze the Access database and look if there exist groups of tables. Example: tables for contacts registration, tables with sales data, ... .
    step 2: create on SQL server the necessary schema's for tgose tables. In the example: schema contacts and sales
    step 3: look at the access tables and cleanup the design: remove unused fields, look at relations, ect. .
    step 4: Look at the data and clean up orphans or double info if existing
    step 5: create manually the new tables on SQL server in the correct schema and choose the most appropriate SQL data types. This way you learn about SQL server and you end up with a thorough knowledge of how the tables on SQL server are designed.
    step 6: set Primary Keys, Foreign keys, constraints and defaults on the SQL server fields
    step 7: import data. This can either be done from the SQL server end using a SSIS package or by linking the new empty tables to Access and use append queries in Access
    step 8: check the data and test if all in the Access application still works.



    Kind regards
    Noëlla

  5. #20
    MEMFBI is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Apr 2020
    Posts
    45
    Thank you.
    Great suggestions!

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Migrating MS Access Database to SQL Server Database.
    By aligahk06 in forum SQL Server
    Replies: 5
    Last Post: 08-31-2020, 09:44 AM
  2. Replies: 8
    Last Post: 09-25-2019, 11:35 AM
  3. Replies: 1
    Last Post: 10-06-2017, 10:22 AM
  4. Replies: 2
    Last Post: 04-27-2016, 06:20 AM
  5. Replies: 11
    Last Post: 10-08-2012, 07:27 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