• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

Look at table and verify records exists

Hi,

I have a form which I want to run an append query if records exists in a temp table.

I saw some code in another database and I trimmed it up to the following, however I"m not certainif I'm on the right track here.  I can't use the ADODB statement in my version of Access 2010 as it doesn't exists.

Can someone advise me the best way to see if records do exists in a table and perhaps pass a True or False variable or soemother indicator?

Public Function chk_table()
Dim rs As ADODB.Recordset
Dim stSQL As String

stSQL = "select * from " & mytable

Set rs = New ADODB.Recordset
rs.Open stSQL, Application.CodeProject.Connection, adOpenStatic, adLockOptimistic

If (rs.RecordCount = 0) Then
   'do nothing
Else
   'do something
End if
End Function
0
MCaliebe
Asked:
MCaliebe
  • 3
1 Solution
 
Surone1Commented:
Public Function chk_table(mytable as String)
Dim rs As ADODB.Recordset
Dim stSQL As String

stSQL = "select * from " & mytable

Set rs = New ADODB.Recordset
rs.Open stSQL, Application.CodeProject.Connection, adOpenStatic, adLockOptimistic

If rs.Eof= true Then
   'no record found, return false
chk_table = false
Else
   'record(s) found, return true
chk_table = true
End if
End Function





msgbox chk_table("table_name")
0
 
Rey Obrero (Capricorn1)Commented:

if dcount("*", "tempTable",<criteria>)> 0 then
   ' do something
end if

0
 
Surone1Commented:
just to be sure to hanlde spaces/reserved words in table names:

stSQL = "select * from [" & mytable & "]"
0
 
Surone1Commented:
or what cap said..
0
 
MCaliebeAuthor Commented:
Simple solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now