Query execution error in VB

[ranjith547]

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

 

[SkipVought]

Hi,

This kind of helps the understandability. BTW, you were missing a SPACE before Select...

    Dim sSQL As String
    
    sSQL = "INSERT INTO DELTEK.ATAC_FL_HDR"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "("
    sSQL = sSQL & "  FLT_HDR_KEY"
    sSQL = sSQL & ", [DATE]"
    sSQL = sSQL & ", MODEL"
    sSQL = sSQL & ", TAIL_NO"
    sSQL = sSQL & ", FLIGHT_LOG_NO"
    sSQL = sSQL & ")"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "SELECT "
    sSQL = sSQL & "  QRY_SEL_TRANS_HDR.FLT_HDR_KEY"
    sSQL = sSQL & ", QRY_SEL_TRANS_HDR.DATE"
    sSQL = sSQL & ", QRY_SEL_TRANS_HDR.MODEL"
    sSQL = sSQL & ", QRY_SEL_TRANS_HDR.TAIL_NO"
    sSQL = sSQL & ", QRY_SEL_TRANS_HDR.FLIGHT_LOG_NO"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM QRY_SEL_TRANS_HDR;"

    sSQL.CommandText

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

last statement...

sqlCmd.CommandText = sSQL

Sorry.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ranjith547]

Hey Thx for reply, by using your code I am not getting any error but I am unable to export the data into SQL server

 

[SkipVought]

Are you sure that your SQL works?

Does just the Select... From work?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ranjith547]

My sql is working actually what I am trying is I am exporting data from Access to sql server for that I amusing the VB

 

[SkipVought]

So you're saying that just excuting the SQL in Access, inserts the correct rows into the SQLServer table. Yes?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ranjith547]

yes, I want that

 

[genomon]

Why not link the SQL table(s) into Access? If you are having problems with ADO command or connection, you can use DoCmd.RunSQL sSql.

All I ask is a chance to prove that money can’t make me happy.