I installed SQL Anywhere 16 Developer and need to import almost 200 Tables originating in Microsoft Access. I have tried the following:

  1. Installed Microsoft SQL Server Express 2012
  2. Imported Access Tables into SQL Server
  3. Added SQL Server to ODBC Source Files (x86)
  4. Created a SQL Anywhere 16 connection / database
  5. Tried the Migration tool: Resulted in copying a bunch .sys and .dbo files that I did not recognize

Is there an easy way "import" tables into SQL Anywhere from MS SQL Server database? The migration tool is a little confusing and I am a "newbie" to writing source code.

In google searching, I see lots of information for importing from SQL Anywhere to SQL Server but not much on importing from SQL Server to SQL Anywhere. I need an easy solution so I can move on to other design & visual basic programming tasks.

asked 03 May '14, 16:45

acuity's gravatar image

acuity
31113
accept rate: 0%

edited 03 May '14, 19:48

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889

1

Please tell us exactly what you mean by "Tried the Migration tool".

The Sybase Central - Tools - SQL Anywhere 16 - Migrate Database wizard does not create any ".sys and .dbo files", it creates and fills table in a SQL Anywhere 16 database...

Sybase Central - create and connect to a running SQL Anywhere 16 database

Tools - SQL Anywhere 16 - Migrate Database...

Create Remote Server Now...

(NOTE: the following uses SQL Server, but you can migrate directly from Access as well... just pick you poison from the list)

Connection information: DSN=MSSTEST;uid=sa;pwd=j68Fje9#fyu489

Create an external login...

Login name: sa

Password: j68Fje9#fyu489

The following SQL will be executed...

CREATE SERVER "MSS" CLASS 'MSSODBC' USING 'DSN=MSSTEST;uid=sa;pwd=j68Fje9#fyu489';

CREATE EXTERNLOGIN "DBA" TO "MSS" REMOTE LOGIN 'sa' IDENTIFIED BY '***';

Select Tables - Add All >>

Which user do you want to own the migrated tables? DBA

(03 May '14, 20:03) Breck Carter

Thanks - I answered my own question (see above)

(03 May '14, 21:12) acuity

Thanks Breck,

I figured it out on my own after doing something else and letting my subconscious think about how to do it.

I used the Open ODBC Administrator to create an ODBC source file (64-Bit) pointing to the Access database I wanted to import into SQL Anywhere 16.

Then I used the Migration Wizard to connect to the Access ODBC source file and selected "all tables" - it worked great!

My last step was to delete all "Proxy" tables.

Bob Sheridan www.acuity-pos-systems.com

permanent link

answered 03 May '14, 21:10

acuity's gravatar image

acuity
31113
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×45

question asked: 03 May '14, 16:45

question was seen: 1,133 times

last updated: 03 May '14, 21:12