Re: refreshing linked tables: NO DAO

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Can't be done with ADO, but it is possible with ADOX. Here's code to link to
every table in a back-end database:

Private Function LinkFEToBEUsingADOX( _
BackendCatalog As ADOX.Catalog, _
PathToBackendDatabase As String _
) As Boolean

On Error GoTo Err_LinkFEToBEUsingADOX

Dim objFECatalog As ADOX.Catalog
Dim objFETable As ADOX.Table
Dim objBETable As ADOX.Table
Dim booStatus As Boolean
Dim strTableNm As String

booStatus = True

Set objFECatalog = CreateObject("ADOX.Catalog")
objFECatalog.ActiveConnection = _
CurrentProject.Connection
For Each objBETable In BackendCatalog.Tables
If Len(objBETable.Type) = 0 Then
strTableNm = objBETable.Name
Set objFETable = CreateObject("ADOX.Table")
objFETable.Name = strTableNm
Set objFETable.ParentCatalog = objFECatalog
objFETable.Properties( _
"Jet OLEDB:Link Datasource") = _
PathToBackendDatabase
objFETable.Properties( _
"Jet OLEDB:Remote Table Name") = _
strTableNm
objFETable.Properties( _
"Jet OLEDB:Create Link") = True
objFETable.Properties( _
"Jet OLEDB:Link Provider String") = _
"MS Access;PWD=Admin;"
objFECatalog.Tables.Append objFETable
Set objFETable = Nothing
End If
Next objBETable

End_LinkFEToBEUsingADOX:
Set objFECatalog = Nothing
LinkFEToBEUsingADOX = booStatus
Exit Function

Err_LinkFEToBEUsingADOX:
booStatus = False
Err.Raise Err.Number, _
"LinkFEToBEUsingADOX", _
Err.Description
Resume End_LinkFEToBEUsingADOX

End Function

I don't know why you'd need this, though. DAO was designed specifically for
use with Access: it's really the appropriate method for working with Jet
databases. Alternatively, you could simply use the TransferDatabase method,
and not need to use either DAO or ADOX.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"rocco" <rocco@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3A59DA10-DA0C-42E4-A86A-9616EA54C74B@xxxxxxxxxxxxxxxx
hello,
is there any method to refresh linked tables without having to code into
DAO?
Anything in ADO or ADOX?
I think there should be something in ADO, but I'm not able to find out
what.
Help very appreciated.

Rocco


.

Relevant Pages

  • Re: Programmatically changing a SQL view in a ADP
    ... One of the differences between DAO and ADO is that DAO combines data ... there isn't a way to do this successfully with ADOX. ... But T-SQL, SQL Server's ... Dim cn As ADODB.Connection ...
    (microsoft.public.access.modulesdaovba)
  • Re: mailmerge and sql
    ... means that you will not be able to see them in a database you open using the ... I believe you may have to use DAO instead of ADO to ... then creates a View containing a UNION query. ... Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)
  • Requery of Listbox does not display new data
    ... add a record to the database. ... Then the Lisbox control's requery method is ... The ADO command is run using a connection string to the mdb containing the ... Dim cmd As ADODB.Command ...
    (microsoft.public.access.formscoding)
  • Re: LongText not supported as output parameter
    ... Text and nText and to not use varcharor nvarcharat first; ... don't use automatic instantiation with ADO; ... Dim cnn as new adodb.connection ... Dim prm as New adodb.parameters ...
    (microsoft.public.sqlserver.programming)
  • Re: Member or Data Member not Found
    ... you've removed the reference to ADO. ... your declaration for tdf is incorrect. ... 2002 only references ADO, but you can add a reference to DAO. ... You must disambiguate as Dim rst As DAO.Recordset. ...
    (microsoft.public.access.formscoding)