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?
Re: How to transfer Access data to SQL server
- Go to SQL Server Management Studio and connect to the database server from where you want to import your Access database.
- Right-click on Databases and select New Database.
- Put the necessary information and click OK.
- Right-click on the new database created, select Tasks and then Import Data.
- On Data Source dialog box, look for Microsoft Access.
- Go to File, look for Access database you want to import and click Open.
- Click Next.
- Click Next and specify how you want to transfer the data from Access to SQL.
- Click Next.
- Click Next and then choose whether to execute immediately or to save it as a file to perform later.
- Click Next and then click Finish.
- Click Close
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
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