Solved

Getting Data Into SQL From Access

Posted on 1998-09-16
5
202 Views
Last Modified: 2010-03-19
I need to move data from an Access database to a SQL Server database, and I have only a few days to get it done.

Knowing Access and being a complete novice at SQL Server (we're on the new Beta version) I decided to do a 'quick and dirty' port from Access by linking to SQL Server and just filling the tables up on the Access side.

But I have run into problems with tables containing Identity columns. They won't accept my data and when I try to use the SET IDENTITY_INSERT {table} ON I get a permissions errors. And yet my connection is for the sa.

What am I missing? Is there a better (faster) way to get my data from Access to SQL Server in an automated process?
0
Comment
Question by:bjames
5 Comments
 

Expert Comment

by:NateT
ID: 1090128
export all the tables data out to tab delim files and use bcp to import the data.  I think the sql 7.0 has a gui interface to do this, but if you have to go the cmd line route and need some help with syntax let me know.
0
 

Expert Comment

by:bert_iligan
ID: 1090129
There are tools available on the net for download, try to browse for MSACCESS97 UPSIZING TOOLS. What you can do with this is you can upsize your existing access tables to the sql server.
0
 

Author Comment

by:bjames
ID: 1090130
I guess I was hoping more to know how it could be done via MS-Access as our company intends to use both SQL Server and Access as back ends to our software. SQL will be for large customers and Access will be for smaller customers who cannot justify the cost of migrating up at this time.

My mandate (for the present) is to create a single product which moves data to either SQL or Access. Right now I have an Access product which moves data to Access, but it isn't so clean going to SQL.

My current solution has been to create a matching set of tables in SQL which do not have Identity columns and move the data there first. Then, within SQL I am able to issue the command SET IDENTITY_INSERT {table} ON and move the data again. But I was wondering if there is a way I can move the data in a single step via Access.

0
 
LVL 1

Accepted Solution

by:
aumdavis earned 100 total points
ID: 1090131
When I need to move tables with data onto SQL Server from Access, I use the following method:

1.  Create a make-table query on the table you want to move.
2.  View the properties of the query in design view.  Change the 'Destination Table' and 'Destination Database' to the table name  you want on SQL Server in the database you want.
3.  Change the 'Dest Connect String' to ODBC;

When you run the query, Access will ask for your username and password (you can also store this info in the connection string parameter).  Access will then create the table and move the data for you.

It's not real fast, but it does work, and it would be easy to automate.  Be careful with datatypes, they don't always convert exactly as you would like them to.

Hope this helps!
0
 

Author Comment

by:bjames
ID: 1090132
It isn't exactly what I was hoping for, but I'm now thinking that what I want to do probably cannot be done. The tables I want to send data to already exist, so creating new tables as I move the data is about the same as what I'm doing now (keeping a copy of tables without the identity constraint to receive the data. In either case I must port the data from the 'temporary' tables into the actual 'live' tables. (Importing data is not necessarily a one-time event.)

But I'm assuming now that pushing data from MS-Access directly into an existing SQL table with an identity constraint is not doable. There must be an intermediate table or file to receive the data first.

Thanks
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 44
Creating SQL script using SQL data and SQL script 8 40
Webservices in T-SQL 3 31
SQL Query Help Top 1 and Distinct? 6 26
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question