CREATE TABLE does not succeed when run in Excel VBA

Connectivity
New Member

CREATE TABLE does not succeed when run in Excel VBA

Hi all

 

I am trying to run teradata code in VBA. Code works fine, but fails when the teradata code includes a 'CREATE TABLE' or 'INSERT INTO'.  Here's my VBA code...

 

 

Dim conn As ADODB.Connection
Dim rec1 As ADODB.Recordset
Dim thisSql As String

Set conn = New ADODB.Connection

conn.CommandTimeout = 7200

userid = "..."
Password = "..."

conn.Open "Provider=MSDASQL.1;Password=" & Password & ";Persist Security Info=True;User ID=" & userid & ";Data Source=edwprd;"

Dim strFilename As String: strFilename = "Q:\...\Table.txt"
Dim strFileContent As String
Dim iFile As Integer: iFile = FreeFile
Open strFilename For Input As #iFile
strFileContent = Input(LOF(iFile), iFile)

Close #iFile

thisSql = strFileContent

 

If the Teradata code is "SELECT... FROM ..." then it works fine, but if it is "CREATE TABLE ... AS ... " or "INSERT INTO ... SELECT ... " then there are no errors, but it does not run the code.

 

Any help would be much appreciated.

 

1 REPLY
Senior Apprentice

Re: CREATE TABLE does not succeed when run in Excel VBA

Hi,

 

I'm tempted to say that there is an error but it is being hidden from you - or your code is not displaying it.

 

It might be worth getting your processing logged by DBQL (query logging). Using that you can see what is actually being received by the TD system. Then you know if the code is being sent and if it is what the error is.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com