Docmd.Runsql Or Cmd.Execute

[WildThing]

Hi,

Setup is this: An access 2000 db with linked oracle tables.
Situation is that 'Insert Into works fine if using Runsql to run the query'
- 'Select works if using the Cmd.Execute'

I cannot get an Insert Into (Table In DB) and Select (From Linked Oracle
Table) to work in the same code/query.

Any Ideas.

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection

connStr = "Pamis"
conn.Open "data source=Pamis; userID=xxx; password=yyy"


sqltext = "INSERT INTO

(App_Work_Item_No) Select App_Work_Item_No
From Loan_App;"

Set cmd = New ADODB.Command
cmd.CommandText = sqltext
cmd.ActiveConnection = conn
Set rs = New ADODB.Recordset


DoCmd.RunSQL (sqltext)


Set rs = cmd.Execute(sqltext)

 

[Graham R Seach]

* Firstly, since you're executing an action query (which doesn't return
records anyway), you don't need a recordset.

* Secondly, you're not using connStr = "Pamis", so delete it.

* Thirdly, you specified CommandText, but not CommandType.

* Fourthly, since you're using ADO, there's no need to use DoCmd.RunSQL
(sqltext) - the cmd.Execute method will do it.

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim sqltext As String

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

conn.Open "data source=Pamis; userID=xxx; password=yyy"

sqltext = "INSERT INTO

(App_Work_Item_No) " & _
"Select App_Work_Item_No From Loan_App"

With cmd
.CommandText = sqltext
.CommandType = adUnknown
.ActiveConnection = conn
.Execute
End With

You can even simplify the whole thing like this:

Dim conn As ADODB.Connection
Dim sqltext As String

Set conn = New ADODB.Connection

conn.Open "data source=Pamis; userID=xxx; password=yyy"

sqltext = "INSERT INTO

(App_Work_Item_No) " & _
"Select App_Work_Item_No From Loan_App"

conn.Execute sqltext

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[WildThing]

Hi, Thanks for this.

I am getting an 'Invalid Table Name' error I think due the sql being execute

* Firstly, since you're executing an action query (which doesn't return
records anyway), you don't need a recordset.

* Secondly, you're not using connStr = "Pamis", so delete it.

* Thirdly, you specified CommandText, but not CommandType.

* Fourthly, since you're using ADO, there's no need to use DoCmd.RunSQL
(sqltext) - the cmd.Execute method will do it.

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim sqltext As String

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

conn.Open "data source=Pamis; userID=xxx; password=yyy"

sqltext = "INSERT INTO

(App_Work_Item_No) " & _
"Select App_Work_Item_No From Loan_App"

With cmd
.CommandText = sqltext
.CommandType = adUnknown
.ActiveConnection = conn
.Execute
End With

You can even simplify the whole thing like this:

Dim conn As ADODB.Connection
Dim sqltext As String

Set conn = New ADODB.Connection

conn.Open "data source=Pamis; userID=xxx; password=yyy"

sqltext = "INSERT INTO

(App_Work_Item_No) " & _
"Select App_Work_Item_No From Loan_App"

conn.Execute sqltext

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi,

Setup is this: An access 2000 db with linked oracle tables.
Situation is that 'Insert Into works fine if using Runsql to run the
query'
- 'Select works if using the Cmd.Execute'

I cannot get an Insert Into (Table In DB) and Select (From Linked Oracle
Table) to work in the same code/query.

Any Ideas.

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection

connStr = "Pamis"
conn.Open "data source=Pamis; userID=xxx; password=yyy"


sqltext = "INSERT INTO

(App_Work_Item_No) Select App_Work_Item_No
From Loan_App;"

Set cmd = New ADODB.Command
cmd.CommandText = sqltext
cmd.ActiveConnection = conn
Set rs = New ADODB.Recordset


DoCmd.RunSQL (sqltext)


Set rs = cmd.Execute(sqltext)

 

[Graham R Seach]

Well why bother with ADO at all?? Just use:

sqltext = "INSERT INTO

(App_Work_Item_No) " & _
"Select App_Work_Item_No From Loan_App"

DBEngine(0)(0).Execute sqltext, dbFailOnError

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi, Thanks for this.

I am getting an 'Invalid Table Name' error I think due the sql being
execute
on / to the adodb connection where the table to be inserted into is a
table
with the access database and not on the connection.

* Firstly, since you're executing an action query (which doesn't return
records anyway), you don't need a recordset.

* Secondly, you're not using connStr = "Pamis", so delete it.

* Thirdly, you specified CommandText, but not CommandType.

* Fourthly, since you're using ADO, there's no need to use DoCmd.RunSQL
(sqltext) - the cmd.Execute method will do it.

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim sqltext As String

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

conn.Open "data source=Pamis; userID=xxx; password=yyy"

sqltext = "INSERT INTO

(App_Work_Item_No) " & _
"Select App_Work_Item_No From Loan_App"

With cmd
.CommandText = sqltext
.CommandType = adUnknown
.ActiveConnection = conn
.Execute
End With

You can even simplify the whole thing like this:

Dim conn As ADODB.Connection
Dim sqltext As String

Set conn = New ADODB.Connection

conn.Open "data source=Pamis; userID=xxx; password=yyy"

sqltext = "INSERT INTO

(App_Work_Item_No) " & _
"Select App_Work_Item_No From Loan_App"

conn.Execute sqltext

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Hi,

Setup is this: An access 2000 db with linked oracle tables.
Situation is that 'Insert Into works fine if using Runsql to run the
query'
- 'Select works if using the Cmd.Execute'

I cannot get an Insert Into (Table In DB) and Select (From Linked
Oracle
Table) to work in the same code/query.

Any Ideas.

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection

connStr = "Pamis"
conn.Open "data source=Pamis; userID=xxx; password=yyy"


sqltext = "INSERT INTO

(App_Work_Item_No) Select
App_Work_Item_No
From Loan_App;"

Set cmd = New ADODB.Command
cmd.CommandText = sqltext
cmd.ActiveConnection = conn
Set rs = New ADODB.Recordset


DoCmd.RunSQL (sqltext)


Set rs = cmd.Execute(sqltext)