refreshing linked tables: NO DAO

[rocco]

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

 

[Douglas J. Steele]

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.

 

[rocco]

Thanks. It was more kind of sort of curiosity. I know it would be better to
stick into DAO for this issue, but was kind of willing to learn also
something new.
Thanks

 

[lu]

Douglas,

we use ADOX to relink password database at a specific location. It failed at
setting property for password "Jet OLEDB:Link Provider String". The Error is
"could not find file". Since the link DB/table was created at some other
locaiton. How to relink table using ADO for this case.
thanks.

 

[Douglas J. Steele]

Do you have a database password? If not, leave out that line.

 

[lu]

Douglas,

We do have the database password.
m_pTable->Properties->GetItem("Jet OLEDB:Link Provider String"
)->PutValue("MS Access;Pwd=test");
we get error "could not find file".

If we leave out above line,
m_pTable->Properties->GetItem("Jet OLEDB:Link Datasource")->PutValue(pszMDB);
it will error out with "Not a valid password".
Is there a way so we can reset the link datasource?

Thanks.

 

[Douglas J. Steele]

Sorry, I don't know C++.