ranjith547
Programmer
I am new to Access and i am migrating data from access to sql server for that I am writing the following code in VB and when I am running I am getting error for invalid aobject name for my access table which is in select statement and code is below
Sub connectTest()
Set sqlConn = CreateObject("ADODB.Connection")
Dim sqlConnStr As String
sqlConnStr = "Provider=SQLOLEDB;Data Source=GPC-WIN2K3STDVM;Initial Catalog=DELTEKCP;User ID=Flm_Imp;Password=in5ta1!;"
sqlConn.ConnectionString = sqlConnStr
sqlConn.Open
Set sqlCmd = CreateObject("ADODB.Command")
sqlCmd.ActiveConnection = sqlConn
sqlCmd.CommandText = "INSERT INTO DELTEK.ATAC_FL_HDR ( FLT_HDR_KEY, [DATE], MODEL, TAIL_NO, FLIGHT_LOG_NO )" _
& "SELECT QRY_SEL_TRANS_HDR.FLT_HDR_KEY, QRY_SEL_TRANS_HDR.DATE, QRY_SEL_TRANS_HDR.MODEL, QRY_SEL_TRANS_HDR.TAIL_NO, QRY_SEL_TRANS_HDR.FLIGHT_LOG_NO FROM QRY_SEL_TRANS_HDR;"
Set returnObj = sqlCmd.Execute
sqlConn.Close
End Sub
can any one help on this, Thx
Sub connectTest()
Set sqlConn = CreateObject("ADODB.Connection")
Dim sqlConnStr As String
sqlConnStr = "Provider=SQLOLEDB;Data Source=GPC-WIN2K3STDVM;Initial Catalog=DELTEKCP;User ID=Flm_Imp;Password=in5ta1!;"
sqlConn.ConnectionString = sqlConnStr
sqlConn.Open
Set sqlCmd = CreateObject("ADODB.Command")
sqlCmd.ActiveConnection = sqlConn
sqlCmd.CommandText = "INSERT INTO DELTEK.ATAC_FL_HDR ( FLT_HDR_KEY, [DATE], MODEL, TAIL_NO, FLIGHT_LOG_NO )" _
& "SELECT QRY_SEL_TRANS_HDR.FLT_HDR_KEY, QRY_SEL_TRANS_HDR.DATE, QRY_SEL_TRANS_HDR.MODEL, QRY_SEL_TRANS_HDR.TAIL_NO, QRY_SEL_TRANS_HDR.FLIGHT_LOG_NO FROM QRY_SEL_TRANS_HDR;"
Set returnObj = sqlCmd.Execute
sqlConn.Close
End Sub
can any one help on this, Thx