Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 20-11-2009
Member
 
Join Date: Jun 2009
Posts: 3,620
How to transfer Access data to SQL server

My problem is that I have an Access database that is linked with several tables located in a remote SQL Server. Via an Access form I import a XL file and I have inserted the fields in the SQL Server table.

For this I created an Access table through but I do not know how to update (added new lines) located in the table from the SQL Server staging table. You would know how? Can you help me how to transfer my Access data to SQL SERVER?
Reply With Quote
  #2  
Old 20-11-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: How to transfer Access data to SQL server

  1. Go to SQL Server Management Studio and connect to the database server from where you want to import your Access database.
  2. Right-click on Databases and select New Database.
  3. Put the necessary information and click OK.
  4. Right-click on the new database created, select Tasks and then Import Data.
  5. On Data Source dialog box, look for Microsoft Access.
  6. Go to File, look for Access database you want to import and click Open.
  7. Click Next.
  8. Click Next and specify how you want to transfer the data from Access to SQL.
  9. Click Next.
  10. Click Next and then choose whether to execute immediately or to save it as a file to perform later.
  11. Click Next and then click Finish.
  12. Click Close
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #3  
Old 20-11-2009
Member
 
Join Date: Jun 2009
Posts: 3,620
Re: How to transfer Access data to SQL server

I have used ADODB, but when running the application an error: syntax error in from clause appears then it is not a simple select query

Code:
Dim oCnSQLSvr As New ADODB. connection 
Dim rsSqlSvr As New ADODB. Recordset 
Dim oCnAccess As New ADODB. connection 
Dim rsAcc As New ADODB. Recordset 
Dim strSql As String 
Set oCnAccess = CurrentProject. connection 
rsAcc. CursorLocation = adUseClient 
rsAcc. CursorType = adOpenStatic 
rsAcc. LockType = adLockReadOnly 
Set rsAcc. ActiveConnection = oCnAccess 
oCnSQLSvr. ConnectionString = "Driver={SQL Server};server=XXXXX;IUD=;PWD=;database=XXXXX" oCnSQLSvr. Open rsSqlSvr. CursorLocation = adUseClient rsSqlSvr. CursorType = adOpenStatic 
Set rsSqlSvr. ActiveConnection = oCnSQLSvr  
rsSqlSvr. Open "test" , oCnSQLSvr, adOpenStatic, adLockOptimistic, adCmdTable 
strSql = "select temp.id,temp.price from temp" Instruction SQL SELECT rsAcc. Open strSql, oCnAccess, adOpenDynamic, adLockOptimistic, adCmdTable
Reply With Quote
  #4  
Old 20-11-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: How to transfer Access data to SQL server

The last argument of this line is incorrect
Code:
rsAcc.Open strSql, oCnAccess, adOpenDynamic, adLockOptimistic, adCmdTable
adCmdTable "said" to ADO strSql that contains a table name.

Try replacing it with adCmdText.
Code:
rsAcc.Open strSql, oCnAccess, adOpenDynamic, adLockOptimistic, adCmdText
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to transfer Access data to SQL server"
Thread Thread Starter Forum Replies Last Post
How to transfer a data on wifi Achilleas Technology & Internet 4 29-12-2010 08:18 PM
How to transfer data on LAN Dumpkin Networking & Security 3 29-11-2010 06:09 PM
Unable to access the send data and received data files in outlook 2010 Ashert Windows Software 4 27-11-2010 10:42 PM
How to transfer PS3 data to another PS3 Marjorie Video Games 7 19-10-2010 05:40 PM
Access data transfer to Sharepoint gives Invalid Internet Address Jacek Windows Software 3 05-06-2009 09:50 PM


All times are GMT +5.5. The time now is 06:40 PM.