Transfer data from SQL table to Access table

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.
maverick0728Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne HerbertIT SpecialistCommented:
Export a .sql file of the data and layout and import it into the Access tables?
maverick0728Author Commented:
Thanks.  Yes, I could do that, but I'd prefer to query SQL to get the data directly.
funwithdotnetCommented:
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!
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

funwithdotnetCommented:
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
funwithdotnetCommented:
To create table & insert:

SELECT * INTO targetTable FROM srcTable
Craig YellickDatabase ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.