Solved

Transfer data from SQL table to Access table

Posted on 2016-08-08
6
38 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
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now