Check To See If Table Exists

[Hackster]

I have written the necessary ALTER TABLE SQL for creating a DB table, but I need to ensure that the table doesn't already exist first. I know that I can just let the routine error out, trap the error, and that way I know it does, but I was wondering if there is an cleaner way to check if a table already exists.

Any suggestions?

 

[Creepers]

This should at least get you through the table iteration:

Public Function Table_Iteration()

Dim i As Integer
Dim Connection_String as string
Connection_String = "Something"

Dim cnn As New ADODB.Connection
cnn.Open connection_string, , , -1

Dim lrs As ADODB.Recordset
Set lrs = cnn.OpenSchema(adSchemaTables)
i = 0
Do While Not lrs.EOF
Debug.Print lrs.Fields(i).Name
Debug.Print lrs.Fields(i).Value
i = i + 1
lrs.MoveNext
Loop
End Function

 

[Swi]

thread222-902271

Swi

 

[Hackster]

Thanks Swi!!

Another related question. How can I tell whether a particular field within a table exists?

 

[DrJavaJoe]

You could either query the SysObjects, SysColumns tables or with the adox method enumerate the columns once the table is found, something like:

'after this line in SWI's example
If UCase$(Table.Name) = UCase$(TableName) Then
'something like this
for i = 0 to table.columns.count - 1
If table.Columns(i).Name = TheFieldYourLookingFor Then
'Field Found
End If
next i



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[Hackster]

Thank you Dr. Java!!