Linking Tables Programtically

[Guest]

I have been succesfully using Dev Ashish's code to re-link tables
programatically with great success.
However, I have just exported this code from a working database, and
imported it into a new database I'm writing, and I get an error message.
The message I receive is:

Compile error:
User-defined type not defined.


The line where the error occurs is:
Function fIsRemoteTable(dbRemote As Database, strTbl As String) As Boolean

Now I'm not too hot on the really technical parts of VBA and I'm struggling
to diagnose this error.
It's very frustrating as I have a databse where it works perfectly, but when
exported to a diferent DB it won't work.

Any help would be appreciated.

Thanks

Neil

 

[Guest]

Make sure that you have set a reference to the DAO library.

- Raoul

 

[Graham R Seach]

Neil,

Sounds like a references problem.

1. From any code module, select References from the Tools menu.
2. You may see an item marked ***MISSING*** - untick it.
3. Find Microsoft DAO 3.x Object Library, and tick it.
4. Click OK.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Thanks Raoul

It worked fine, don't remember having to that in the past, but it was 18
months or so ago.

Neil

 

[Guest]

Hi.. I'm also trying to use the Dev's codes but with no luck. Im new to VB as
a starter.

I copied Dev's codes into a new module called fRefreshLinks() but i get an
error. I suppose I need to create a table called fGetLinkedTables. Can some
guide me where I can get a sample of this table so I can create it locally?
Also, is there anything else I should do or create?

thanks !
Rob

 

[Alex Dybenko]

Hi,
you can not name module same as function name. give module other name, say
mdlRefreshLinks

 

[Guest]

Thanks Alex..
I renamed the function as modRefreshLinks..It runs a few steps but it halts
with error "Compile error: Sub or Function not defined". It stops in this
segment:

Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")
strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")

fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:=strIn, _
Flags:=ahtOFN_HIDEREADONLY)
End Function

Hope to hear your suggestion(s).

Thanks,

Ray

 

[Douglas J Steele]

You missed the comment in the 4th paragraph, stating that you also needed to
include the code to invoke the GetOpenFileName dialog, and linking you to
http://www.mvps.org/access/api/api0001.htm

 

[Guest]

Doug, thanks !
I've added the code and named it modGetSaveFile. But now, Im not sure how to
proceed, ie. how to call the module, or loop them together, etc. I'm sorry
Doug, as I'm really new to VB.

But I always appreciate your help (and others too!)

Rob

 

[Douglas J. Steele]

How to call which module? The first code you copied calls the second one if
it's necessary.

 

[Guest]

Thanks Doug !! ...it seems to be working. I just need to tweak it more to
customize it based on my needs.

I have a followup question, on a different code. I copied Dev's codes on how
to "Relink ODBC tables from code". It halt when I run it. It stops at "
rs.FindFirst ".
Do I need to add something in my Tools> References? Im using Access2000.

Thanks again
Rob

 

[Douglas J Steele]

Most of Dev's code was written under Access 97, before ADO was a factor. I'm
guessing you're using a new version and have references set to both ADO and
DAO. Since there's an object named Recordset in both the ADO and DAO models,
you need to disambiguate.

Change "rs As Recordset" to "rs As DAO.Recordset".

What's happening is since your reference to ADO is higher in the list of
selected references than the one to DAO, rs As Recordset results in an ADO
recordset. (If you wanted to be sure you were getting an ADO recordset, btw,
you'd use rs As ADODB.Recordset)

 

[Guest]

Hi Doug..Thanks for your tips.. I havent tried your suggestions coz got so
busy with work but I'll try it as soon as I get a chance. Just thought to let
you know that I appreciated all your help.

Thanks,
Rob