VBA to Check to see if an ODBC table exists

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
Hey there,

Using VBA in a module, I need to check to see if a table that i want to connect to via ODBC exists. is this possible?

I'm using DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=AAAA;DBQ=BBBB;PWD=Oracle111;SERVER=CCCC", acTable, "SPECIFICtableName", "tableName", False, False to link to the desired table.

But I want to check to see if "SPECIFICtableName" exists before.

Thansk
Terry
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
I think regardless of what you do, you're going to have to add error checking, the only way to test for a table is to try to open it, and trying to open a table that's not there will give you an error. So either way, you need to use error checking. May as well just handle it with the code you've got rather than write more.

Code:
OnError goto Errchk

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=AAAA;DBQ=BBBB;PWD=Oracle111;SERVER=CCCC", acTable, "SPECIFICtableName", "tableName", False, False

exit Sub

Errchk:
     Msgbox "unable to connect"

End Sub
 
Upvote 0
I like that but I’m still struggling with my over all procedure. Maybe you can give a suggestion. Overall goal is to auto load data from an odbc connection where the name of the desired table changes daily. Once the table is linked I append to a consolidated table (“tbl_Phone_Stats_by_Day”) then delete the recently linked table an move to the next table to link, append etc then to the next to link, append etc. The goal is to run this nightly up to yesterday table to link and append. I currently grab the max of the “tbl_Phone_Stats_by_Day” to string the name of the desired linked table.

The issue is that certain tables do not exist. More specifically the are no tables with Mondays data stamp as well as holidays.

Here is my code which function properly is I didn’t have to skip certain days (mon and holiday). Since holidays change I tried to implement the error handling you suggested.

I’m basically not looping correctly to find the next desired load date and need to set up to loop betw LastLoad + 1 to Yesterdays date

Thanks
Tuk

Code:
Public Function LinkAndAppend()
DoCmd.SetWarnings False

DoCmd.OpenQuery ("qry_tbl_tsktsrhst_DATES_Delete")

'append LastLoads date + 1
DoCmd.OpenQuery ("qry_Next_Table_Date_APPEND")

Dim rsTableName As Recordset
Dim strTableName As String

Dim fldsDate As DAO.Fields
Dim fldDate As DAO.Field

Dim fldsDateFormat As DAO.Fields
Dim fldDateFormat As DAO.Field

'queries the LastLoads date + 1
Set rsTableName = CurrentDb.OpenRecordset("qry_Create_ODBC_Tbl_Name")

Set fldsDate = rsTableName.Fields
Set fldDate = fldsDate("table_Date")

Set fldsDateFormat = rsTableName.Fields
Set fldDateFormat = fldsDateFormat("NextLoadDateFormat")

'record**** will always be one, namely, LastLoad + 1
If rsTableName.RecordCount > 0 Then
With rsTableName
    .MoveFirst
    Do While Not .EOF
     
Set strFullTableName = fldDate
Set strFullTableNameFormat = fldDateFormat

On Error GoTo Errchk
        DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=ServerName;DBQ=USER;PWD=MYPSWD;SERVER=CCCC", acTable, "tsktsrhst" & strFullTableName, "tsktsrhstDAILY", False, False

'consolidate and append to main table
        strSql = "INSERT INTO tbl_Phone_Stats_by_Day ( tsr, listid, call_date, SumOftot_calls, SumOftime_connect, SumOftime_paused, SumOftime_waiting, SumOftime_deassigned, SumOflogon_time, logoff_time, SumOftime_acw ) " & vbCrLf & _
        "SELECT tsktsrhstDAILY.tsr, tsktsrhstDAILY.listid, tsktsrhstDAILY.call_date, Sum(tsktsrhstDAILY.tot_calls) AS SumOftot_calls, Sum(tsktsrhstDAILY.time_connect) AS SumOftime_connect, Sum(tsktsrhstDAILY.time_paused) AS SumOftime_paused, Sum(tsktsrhstDAILY.time_waiting) AS SumOftime_waiting, Sum(tsktsrhstDAILY.time_deassigned) AS SumOftime_deassigned, Sum(tsktsrhstDAILY.logon_time) AS SumOflogon_time, Sum(tsktsrhstDAILY.logoff_time) AS SumOflogoff_time, Sum(tsktsrhstDAILY.time_acw) AS SumOftime_acw " & vbCrLf & _
        "FROM tsktsrhstDAILY " & vbCrLf & _
        "GROUP BY tsktsrhstDAILY.tsr, tsktsrhstDAILY.listid, tsktsrhstDAILY.call_date;"

        DoCmd.RunSQL strSql
        DoCmd.DeleteObject acTable, "tsktsrhstDAILY"
rsTableName.MoveNext
Loop
End With

End If

Errchk:
'table does not exist
' need to create the funtionality to add a date and loop until yesterdays date

DoCmd.SetWarnings True
End Function
 
Upvote 0
I'm sorry, I spaced out there for a second. I saw you were linking to an Oracle database and assumed you weren't using Access, duh.

Here is a routine to test if a table exists in Access:

Code:
Public Function fTableExists(TblName As String) As Boolean
'determine whether table exits in this database

Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
On Error Resume Next
Set td = db.TableDefs(TblName)
If Err.Number <> 0 Then
    fTableExists = False
Else
    fTableExists = True
End If

End Function
 
Upvote 0
well actually you were correct. i am actually trying to figure out if a table exists in the oracle database. so the error handling works fine but i was asking if you have other suggestions on how to load multiple table from the oracle database in a consolidated ms access table.

the oracle table names are date specific and are not in cronological order (missing mondays and holidays)....this is where the error handling can help.

does that make sense?
 
Upvote 0
How about:

Code:
DoCmd.SetWarnings False

DoCmd.OpenQuery ("qry_tbl_tsktsrhst_DATES_Delete")

'append LastLoads date + 1
DoCmd.OpenQuery ("qry_Next_Table_Date_APPEND")

Dim rsTableName As Recordset
Dim strTableName As String

Dim fldsDate As DAO.Fields
Dim fldDate As DAO.Field

Dim fldsDateFormat As DAO.Fields
Dim fldDateFormat As DAO.Field

'queries the LastLoads date + 1
Set rsTableName = CurrentDb.OpenRecordset("qry_Create_ODBC_Tbl_Name")

Set fldsDate = rsTableName.Fields
Set fldDate = fldsDate("table_Date")

Set fldsDateFormat = rsTableName.Fields
Set fldDateFormat = fldsDateFormat("NextLoadDateFormat")

'record**** will always be one, namely, LastLoad + 1
If rsTableName.RecordCount > 0 Then
With rsTableName
    .MoveFirst
    Do While Not .EOF
     
Set strFullTableName = fldDate
Set strFullTableNameFormat = fldDateFormat

On Error GoTo Errchk
        DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=ServerName;DBQ=USER;PWD=MYPSWD;SERVER=CCCC", acTable, "tsktsrhst" & strFullTableName, "tsktsrhstDAILY", False, False

'consolidate and append to main table
        strSql = "INSERT INTO tbl_Phone_Stats_by_Day ( tsr, listid, call_date, SumOftot_calls, SumOftime_connect, SumOftime_paused, SumOftime_waiting, SumOftime_deassigned, SumOflogon_time, logoff_time, SumOftime_acw ) " & vbCrLf & _
        "SELECT tsktsrhstDAILY.tsr, tsktsrhstDAILY.listid, tsktsrhstDAILY.call_date, Sum(tsktsrhstDAILY.tot_calls) AS SumOftot_calls, Sum(tsktsrhstDAILY.time_connect) AS SumOftime_connect, Sum(tsktsrhstDAILY.time_paused) AS SumOftime_paused, Sum(tsktsrhstDAILY.time_waiting) AS SumOftime_waiting, Sum(tsktsrhstDAILY.time_deassigned) AS SumOftime_deassigned, Sum(tsktsrhstDAILY.logon_time) AS SumOflogon_time, Sum(tsktsrhstDAILY.logoff_time) AS SumOflogoff_time, Sum(tsktsrhstDAILY.time_acw) AS SumOftime_acw " & vbCrLf & _
        "FROM tsktsrhstDAILY " & vbCrLf & _
        "GROUP BY tsktsrhstDAILY.tsr, tsktsrhstDAILY.listid, tsktsrhstDAILY.call_date;"

        DoCmd.RunSQL strSql
        DoCmd.DeleteObject acTable, "tsktsrhstDAILY"
        
         Errchk:
             'do nothing, just loop to next table

rsTableName.MoveNext
Loop
End With

End If

DoCmd.SetWarnings True
End Function
 
Upvote 0
so i actually created a qry which shows results from LastLoad +1 to Now()-1. This gives me all dates needed. then i step thru the entire list loading those table that exist utilizing the original error handling suggestion.

my issue now is that the error Handling only works once.

so when i step thru my dates the "On Error GoTo Errchk" gets me past the msgbox pop up (table doesnt exist) for ONLY the first Monday or Holiday......so it gets hung up by the msg box on the 2nd time around.

any ideas?
 
Upvote 0
You're using the code I posted in post #6?

Try clearing the error before resuming the loop, like this:

Code:
 Errchk:
             'do nothing, just loop to next table
            Err.Clear

Sorry, I'm not an OnError expert, I normally just poke around enough to get what I want.
 
Upvote 0
alright that worked but it appears that maybe it is too fast as wierd as that sounds. when i trigger the code i get an ODBC call fail error on the DoCmd.RunSQL strSQL but if i key thru it (F8) i do not get the error on that line of code. also after it break i can hit F8 and it proceeds.

is there a time pause i can add in in vba.....just a guess.

T
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top