Doing a drop table SQL Query

[JazzBass]

Hi gang,
I've never used VBA for Databases before and what I'm wondering is how do you run a drop table query (SQL Pass-Through) to a ODBC database.
Here is my info:

Variables:
Selected by User from Combo Box
TableSpace
TableName

Drop Table Tablename

I need a serious walk through of the entire process.
How do setup a connection to the ODBC database?
After that, how do you pass the query?

Thanks for the help,
JazzBass

------------------
Beginner VB Programmer

[JHausmann]

Man, it's been forever since I coded to the ODBC API....

In previous posts, you got the information you need to do an ODBC connection.

To issue the drop table, you'll need to use (easiest) the SQLExecDirect API.
Format is
returncode = SQLExecDirect(handle, SQLstring, length of SQLstring)

The types are as follows:

Handle = HSTMT (i think it's a VB long)
SQLString = UCHAR FAR *
length of SQLstring = SDWord i think it's a VB long)

[JazzBass]

JHausmann,
Thanks for the reply. I'm so confused and tired that nothing makes since. Could you relate what info it was to connect to a ODBC Database? Also, could you explain a little more about that ODBC API call you suggested. Please forgive my ignorance. My signature says it all :)
If you could include all the info using my variables in your reply that would be great!
Thanks so much,
JazzBass

------------------
Beginner VB Programmer

[JHausmann]

Try opening the database object as you have before, then doing

databaseobject.Execute "Drop table tablename", dbExecDirect


If the above doesn't work, the following should work by coding to the ODBC API directly.

Dim returncode As Integer
dim hEnv As Long
dim hDB As Long
dim DSN As String
dim UID As String
dim PWD As String
dim SQLstring As String

'***
'OPEN Database
'***

' Allocate the environment handle.
returncode = SQLAllocEnv(hEnv)
' Allocate a connection handle.
returncode = SQLAllocConnect(ByVal hEnv, hDB)

DSN = "MyDSN"
UID = "MyUserID"
PWD = "MyPassword"

' Connect using the arguments provided.
returncode = SQLConnect(hDB, DSN$, Len(DSN$), _
UID, Len(UID), PWD, Len(PWD))

' Test for a successful connection.
If returncode = SQL_SUCCESS Or returncode= SQL_SUCCESS_WITH_INFO Then
Print "Connection open"
Else
Print "Connection did not open"
End If

DescribeError hEnv, hDB, 0

'***
'DO WORK
'***

SQLstring="Drop table tablename"
returncode = SQLExecDirect(hDB, SQLstring, len(SQLString))

'***
'CLOSE and cleanup
'***

'disconnect from the database
returncode = SQLDisconnect (hDB)
'Free the connection handle.
returncode = SQLFreeConnect (hDB)
' Free the environment handle.
returncode = SQLFreeEnv (hEnv)



Portions of the above code come from Visual Basic Books online (see the ODBC API section).

[This message has been edited by JHausmann (edited 08-19-1999).]

[JazzBass]

JHausmann,
Thanks so much for the help. I have one problem though. I'm using Access's VBA and I have no reference for the API calls. I get the message "Sub or Function not Declared" or something to that effect. I guess it basically means I need to Declare those functions and I have no reference for them.
Would you be kind enough to post what the format of the calls are.
Thanks,
JazzBass

------------------
Beginner VB Programmer

[JHausmann]

Global Const SQL_NTS As Long = -3
Global Const SQL_MAX_MESSAGE_LENGTH As Long = 512
Global Const SQL_NO_DATA_FOUND As Long = 100
Global Const SQL_SUCCESS As Long = 0
Global Const SQL_CHAR As Long = 1
Global Const SQL_C_CHAR As Long = SQL_CHAR
Global Const SQL_CLOSE As Long = 0

Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal henv&, phhdbc&) As Integer
Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv&) As Integer

Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal hDbc&, phstmt&) As Integer

Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hdbc&) As Integer

Declare Function SQLError Lib "odbc32.dll" _ (ByVal henv&, ByVal hDbc&,_ ByVal hstmt&, ByVal szSqlState$, pfNativeError&, _ ByVal szErrorMsg$,_ ByVal cbErrorMsgMax%,
pcbErrorMsg%) As Integer

Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal hstmt&, ByVal szSQLSTR$, ByVal cbSQLStr&) As Integer

Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hdbc&) As Integer

Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal henv&) As Integer

Declare Function SQLFreeStmt Lib
"odbc32.dll" _ (ByVal hstmt&, ByVal fOption%) As Integer

[JazzBass]

JHausmann,
Thanks so much for your patience. Thanks for the code, but I got one problem. The SQLConnect function is not declared.
What is the declaration for that function?
I need that declaration for this line:

returncode = SQLConnect(hDB, DSN$, Len(DSN$), UID, Len(UID), PWD, Len(PWD))

Thanks so much.

PS - Is the stuff the same that would be found on the MSDN lib on CD? Just wondering how much of difference there is between VBA and VB6.
Thanks,
JazzBass

------------------
Beginner VB Programmer

[JHausmann]

Declare Function SQLConnect Lib "odbc32.dll" (ByVal hConnect&, ByVal szDSN$, ByVal cbDSN%, ByVal szUID$, ByVal cbUID%, ByVal szAuthStr$, ByVal cbAuthStr%) as Integer

Yes, all this stuff is on the MSDN CD.

[JazzBass]

JHausmann,
Thanks for your patience,
I really appreciate your time. I can't get it to run the query. It's almost like it's skipping over the sql command, but no error messages are comming up. Who knows; I'll probably bring my Library CD here to work and work on it here.
If you have any other suggestions, please let me know.
JazzBass

[JHausmann]

Check the return code, it should be giving you the error...

[JazzBass]

Thanks,
I think that was part of it. I found some stuff on the MSDN library at home and now it's only a matter of installing it here at work.
Thanks so much for your help
JazzBass