Execute an external Query

[EnS]

Hi guys,
I'm not sure if this has been covered or not, but I was wonding how I can execute a query in one database from an external database? Can this even be done? I think I would use ADODB.Command but am not sure. Can anyone help?

Thanks for the advise.
ERM

 

[vbajock]

I set up a function that uses the execute method of the connection object after establishing the connection to the remote database. I pass the function a SQL string. For example:
I don't think you can execute a saved query on a remote database using ADO. If someone knows how, I would love to hear it too.

sql="UPDATE COMPFILE SET
sql=sql+" COMPFILE.COMP_REPORT_NAME = 'Daily Report for 1/31/03';"

call TablesSql(sql)


Sub TablesSql(ByVal sqlstr As String)
On Error GoTo err_TablesSql

TablesDb.BeginTrans

TablesDb.Execute sqlstr, , adCmdText Or adExecuteNoRecords
TablesDb.CommitTrans 'begin/com trans to flush data to disk

Exit Sub
err_TablesSql:

MsgBox Str$(Err) + " " + Error$ + " " + sqlstr
TablesDb.CommitTrans
Exit Sub

End Sub

 

[EnS]

Thanks for the reply. I used the EXECUTE command like you suggested an it works perfect. And, in all honesty, I think it was easier.

Thanks again. ERM