Reconnect linkede tabeller

du bliver nok nødt til at gøre det manuelt; du kan ikke få SQL Server til at tage det hele med, når du overfører fra en Access.

http://www.eksperten.dk/spm/220995

Kig under Tools/Database utilities/Linked Tables Manager

Der kan du få refreshed links.

Terry> You must explain how the code works. The "Dim dbsCurrent As Database" and "Dim tdfLinked As TableDef" gives me "User defined type not defined" Im using Access 2000. I guess I have to place the code in a modul, or?

Place this in a module and then alter all places starting with The

for example TheTableName needs to be re-named to a table you want to re-link.
In this SIMPLE example if the linked table doesnt exist the a new linked table is made. (if it exists in the dB)

You also need a reference to DAO

You could then modify the code to loop through all of your tables.


Sub RefreshLinkX()

Dim dbsCurrent As Database
Dim tdfLinked As TableDef
Dim sTableName As String
Dim sConnect As String

    On Error GoTo RefreshLinkX_err
   
    Set dbsCurrent = CurrentDb

    sTableName = "TheTableName"
    sConnect = "ODBC;DATABASE=TheDatabaseName;UID=TheUserID;PWD=ThePassword;DSN=TheDataSource"
   
    'Refresh link on existing table
    dbsCurrent.TableDefs(sTableName ).RefreshLink

    dbsCurrent.Close
   
    Exit Sub
   
RefreshLinkX_err:

    'Create a new linked table
    Set tdfLinked = dbsCurrent.CreateTableDef(sTableName)
    tdfLinked.CONNECT = sConnect
   
    tdfLinked.SourceTableName = sTableName
    dbsCurrent.TableDefs.Append tdfLinked
   
    Resume Next
   
End Sub

I still get "Compile Error: User-defined type not defined" on "Dim dbsCurrent As Database". Is the table names the only thing there need to be renamed beside the ODBC connection string?

you must have a reference to Microsoft DAO. You do this in Tools References menu while in code view.

Is any user then able to use this form. I surpose that the settings i make will work for all users, or???

as the code is at the moment then only when a NEW table link is made is the User name and password included in the CONNECT string, these will obvioulsy need altering for each user unless of course youuse the SAME user name and password.

If the linked table already exists then you will be prompted for the user name and password.

If i pull out the netcable run the code it runs the error part but when i connect the net cable it keeps running the error part. It is this problem i would like to solve.

skov_p>I can not see what your code looks like, or your ODBC Data Source OR how you have your database set up.
You MUST make sure that the User exists AND that they have permissions to the table you are trying to LINK to.
Try DELETING the linked table from the Access database and runing the code again. IT DOES WORK I have tried it, but I know my table and user exists and with the correct permissions!

skov_p
take a look at the NEW example I have posted in http://www.eksperten.dk/spm/220995

Ok but the code is a little tricky for me. I will have a look at it.

Forget the code, it doesnt need altering!

Just make the table as it explains then alter the data so it suits your needs.

If we forget the ODBC for a while. How do i reconnect to linked Access table. I have a Access table on a Network drive if I pull out the network cable I get a red cross on the network drive in "My Computer". Im not able to connect to my linked tables on the network drive unless I dblclick on the network drive first.

skov_p>Dont mis-understand m ebut the ORIGINAL question was how to refresh a linked ODBC connection, can we do one thing at a time please? If we get one thing working then we can go on from there!
If you dont have a network connection then you arent going to get anywhere, so lets assume that you do.
Have you tried the LAST sugestion I made in the OTHER question?
http://www.eksperten.dk/spm/220995

I have tried it and maked some of it work but i decided to look at it later. Dont worry you will get your points but my question says "Jeg henter data fra en anden Access db og en MS SQL DB." When i wrote Access i dident assume that it was possible to ODBC connect to another Access DB. Is it

Dim db As Database

Set db = Currentdb

Loop through your tables here
db.TableDefs("TabelName").RefreshLink


db.close
set db = Nothing

Ok, That works fine. In the example in http://www.eksperten.dk/spm/220995 you say something about Creating a table. The one i want to connect to allready exists. Do i then have to change the code?

DSN = CustomerOptions
Local table name= HwKeys
Table on SQL Server = dbo.kundeoptioner

I can customize the DSN and the local table name as I want to but the name of the table on the SQL server cant be changed.
I assume that the local table is the one in my Access DB

skov_p>The table you have to create holds the names of the tables you want to re-link and the DSN to use.
You should NOT need to change the code for the SQL (ODBC) tables but you will need something else for your LINKED Access tables.

The local table name is just an "alias" name for the SQL server table.

When you say "LINKED Access tables" do you then mean the tables from the other Access Db? I have solved that problem. Now I think I know what you mean in http://www.eksperten.dk/spm/220995 and I also think I can figure out the code. I will check it on Monday.

Yes I mean the tables from the other Access dB.

Ok it also works. One last thing. Is it possible to avoid the prompt about user id for the linked table?

Concerning User ID! I assume you mean with SQL (ODBC) linked tables.
Well if it is NOT included in the DSN then you will have to program it into the connect string (TheUserID)

sConnect = "ODBC;DATABASE=TheDatabaseName;UID=TheUserID;PWD=ThePassword;DSN=TheDataSource"

We are wery close now. I make a Dcount("FieldName","Tbl") to se if there is any connection. If no connection then I try to refreshmylinks. Is there another way to check the connection. Dcount ask for user id. The data i get from my linked ODBC table is a RowSource for a list box. When i set the RowSource i get two prompts for user id. One is ok but 3...

Skov_p>I dont think I understand EXACTLY what it is your trying to do!

The table is supposed to contain information ABOUT the tables (ODBC) which ARE linked and need to be refreshed.
In one of the first answers I gave in http://www.eksperten.dk/spm/220995 it is ALSO possible to create new LINKED tables if they do NOT already exist. BUT in both cases the information MUST exist in the table so I dont quit understand what  Dcount("FieldName","Tbl") does!

The tables MUST be linked BEFORE you open the form containing the list box. When you re-link your ODBC tables you MUST include the user name and password! You can make a little form where the user enters their user name and passwrod and then the Relink routine can take the values from that form. This LOGIN form MUST be the first form you open.

The Dcount just check if there is a connection. If not then MyRefreshLink.
But i have solved that problem by trying opening a recordset instead of Dcount. If it cant open the recordset then i try MyRefreshlink. I can add the user id in the recordset.open... so no prompt there. Now i could try to load the data into the listbox using a recordset then i dont get any prompt there either. How is it possible to add data to a listbox whitout ListBox.RowSource(SQLstr) and whitout prompt for user id

You can use the listbox wizard to set th elistbox up for you. You MUST ensure that ALL tables are linked/reflinked BEFORE using ANY forms which use these tables. So as I suggested earlier, make a login form and do it here and have this form as the start-up  form!

The ListBox Wizard have problems using the ODBC source. "ODBC--connection to "MyDNS" failed" I have no problem opening the linked table by dblclicking on it.

BEFORE using the listbox wizard MAKE SURE that the table is linked and that you can open it and see data. When you can, try using th ewizard again, you should not get an ODBC error now!

I have on problems opening the table but the wizard do

I have NO problems opening the table but the wizard do

what problem are you having? Is this with a LINKED Access table or is it an ODBC linked table?
It isnt very easy for me to help here, as I cant see waht is happening and if you are linking ODBC tables then it will not help you sending me the dB to look at!

Its an ODBC linked table. After i have decided which columns i want in the ListBox i press "Next". Then i get "ODBC--connection to 'mydsn' failed". If i use object.rowsource there are no problems.