Solved

Transfer data from SQL table to Access table

Posted on 2016-08-08
6
58 Views
Last Modified: 2016-08-08
I am using SQL server 2012, Access 2010.
I need to transfer data from the SQL database table named:  vntgCust
to a Access table named:  tblCust

All the fields in the SQL table should copy into the access table.

What statement can I use to accomplish this?

Note:  I don't want to setup ODBC connection names to the SQL database on each computer running this.
0
Comment
Question by:maverick0728
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 6

Expert Comment

by:Wayne Herbert
ID: 41747280
Export a .sql file of the data and layout and import it into the Access tables?
0
 

Author Comment

by:maverick0728
ID: 41747300
Thanks.  Yes, I could do that, but I'd prefer to query SQL to get the data directly.
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41747344
You should be able to import a SQL table via ODBC Data import directly to an Access table. You have to setup a connection to import the data from SQL Server, but you don't need it once the data is imported. Once the database is chosen, just pick the desired table to import.

If you want to import without a connection, export the SQL table to one of the import file formats supported by Access. Namely Excel, XML and text. I believe Access 2010 can import XML.

You can format a SQL table in XML thus:
SELECT * FROM sourceTable FOR XML PATH

Save the result to a file and import it into Access.

Good luck!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41747350
A direct SQL command would look like:

INSERT INTO targetTable
SELECT * FROM srcTable

You may or may not need to specify the column list ...
INSERT INTO targetTable (column1, column2, ...)
SELECT column1, Column2, ... FROM srcTable
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41747360
To create table & insert:

SELECT * INTO targetTable FROM srcTable
0
 
LVL 11

Accepted Solution

by:
CraigYellick earned 500 total points
ID: 41747362
You can create a linked table definition from VBA code, using an ODBC connection string that exists in the code module. That is, the connection is not defined by the local workstation. Once you have the linked table definition you can use it as a FROM table and perform  any action needed, including creating a local table copy.

Sub CreateLinkedTable()
  Dim con As String
  con = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=myServer;Trusted_Connection=Yes;Database=Northwind;Table=dbo.Customers"
  Dim t As DAO.TableDef
  Set t = CurrentDb().CreateTableDef
  t.Connect = con
  t.SourceTableName = "Customers"
  t.Name = "LinkToCustomers'"
  CurrentDb.TableDefs.Append t
End Sub

To get the ODBC connections string, use Access to manually created a linked table ref. In the properties window you'll see a copy of the connection string.

-- Craig
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

762 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