Display Table Type in form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database where the tables may be imported sometimes and linked other
times. I have a form with TextBox1.

I want to find what type of table ("Linked Access" or "Table") I am
currently using and place that value in TextBox1. I also would like to find
the date on which it was imported or linked.

Can you help me?
 
I have a database where the tables may be imported sometimes and linked other
times. I have a form with TextBox1.

I want to find what type of table ("Linked Access" or "Table") I am
currently using and place that value in TextBox1.
Click to expand...

You can view the tabledef's Connect property:

Currentdb.Tabledefs("tablename").Connect

This will be an empty string for a local table and the connection string
otherwise.
I also would like to find
the date on which it was imported or linked.
Click to expand...

Access does not record that information - it's up to you to do so!

John W. Vinson [MVP]
 
Can I use this to fill the textbox??

iif(Currentdb.Tabledefs("tblWorkOrder").Connect Is Null,
me.textbox1="Import", me.textbox1="Linked")
 
Can I use this to fill the textbox??

iif(Currentdb.Tabledefs("tblWorkOrder").Connect Is Null,
me.textbox1="Import", me.textbox1="Linked")
Click to expand...

Try it, but I doubt it. The CurrentDb collection is only available in VBA
code. Here's a quick little function that may do the job for you:

Public Function TableType(strTablename) As String
Dim db As DAO.Database
Dim tdf As DAO.Tabledef
On Error GoTo Proc_Error
Set db = CurrentDb
Set tdf = db.Tabledefs(strTablename)
If Len(tdf.Connect & "") = 0 Then
TableType = "Import"
Else
TableType = "Linked"
End If
Proc_Exit:
Exit Sub
Proc_Error:
Select Case Err.Number
Case 3265 ' item not found in collection
TableType = "No Such Table"
Case Else
MsgBox "Error " & Err.Number & " in TableType:" _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub

You could then set the control source of a textbox to

=TableType("tblWorkOrder")

or

=TableType(txtTableName)

where txtTableName is a textbox on the form containing the table name.

John W. Vinson [MVP]
 
The iif didnt work..... but this did

If CurrentDb.TableDefs("oep550e").Connect = ";Database=C:\Documents and
Settings\ray\desktop\QA Audits Database 3.mdb" Then Me.Text0 = "Real Time"

If CurrentDb.TableDefs("oep550e").Connect <> ";Database=C:\Documents and
Settings\ray\desktop\QA Audits Database 3.mdb" Then Me.Text0 = "Snapshot"

Thanks for the help
 
Back
Top