Reconnecting ODBC linked tables in MS Access via VBA


Hello,

I've spent hours trying to find a solution without any luck!

*** Situation ***
I have a number of ODBC linked tables in an MS Access 2003 application. I want to be able to switch between source databases; i.e. between production and development databases, easily.

*** What works ***
Open Linked Table Manager;
Select the numerous linked tables;
Select Always Prompt For a New Location;
Click OK;
Provide the new DSN....

This works fine, but is rather manual, and does not provide for hands-free deployment. I'd like to be able to do this from code. All I need to do is change the Connect property on the tabledef, how ever, this property is read-only on existing tables! **** Microsoft!

The only method that turned up through hours of research is to drop the old table, then to connect the new table. This of course would be a nightmare, because then I would have to rebuild all of the security settings on the table, all the captions on each field, and all of the combo box lookups on some of the fields! Not gonna happen!

Anyone who can solve this riddle is a true guru!

Thank you,

Daniel Odulo
http://www.Odulo.com/Consulting/


Sponsored Links:



I'm using MS access and Excel 2000. I have an Excel spreadsheet that contained 8 columns, the first column has all cell format as Number, the rest of the column is set as custom date format of 'dd/mm/yyyy'. When I create a linked table in MS Access, the data types does not matched my excel spreadsheet columns, the 'Number' data type is a double and I want a Long Integer in Access, and the custom date format become text datatype but I wanted a DateTime datatype. Is there any work around this? Seems like it is a common problem.

Your prompt response is greatly appreciated!

Thanks in advance!
Martina




Hi,

I have a lot data to append to ODBC linked table in MS Access. I want to know that which way is faster to append the records.

if I append the data into ODBC linked table,
1) create the one query (append) to insert the records into ODBC linked table
2) use the VBA code (DAO/ADO) to insert the records into ODBC linked table

which way is rather faster?




Hey guys - me again!!

We have a number of databases that use MS Access for both front and back ends, and I need to write a script that deletes a linked table from the front end and replace it with the relevant table in a seperate SQL backend.

I have no clue on where to start with this, so if anyone can pass any suggestions my way I'd be most greatful!

Thanks,

Jeff.




Hi All,

I am developing Windows Application using C#.net with MS ACCESS 2007 as my Database. During Development I have entered so many records in my database.I am having tables with datatype as AutoNumber..

Now my requirement is I want truncate tables I mean I want to delete all records and reset autonumber values..

In sql server database, Truncate keyword is there to truncate the tables...

But in MS Access I could not find this keyword...

So, Please any one tell me how to truncate tables in MS ACCESS 2007..

Thanks in advance..




How to trough VBA code to create dynamic table in MS Access 2003? Any sample code is welcome. TQ




Hello,

I would like to know if there is a way to control different properties of a chart in MS ACCESS from VBA such as the scale of the axis, Title, Legend, variables...

Merci.


R1




Hi,

I created the table in SQL Server, and the fieldnames are as

field0 => char (and setup this field as primary key)
field1 => char
Select => bit

After that, I used ODBC link the table to MS Access database,
I input the data in the linked table in MS Access, then I jump to next record, and then go back the previous record, and then edit the information, but, it give out the error message as follow

Write Conflict
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

I tried to remove the fieldname "Select" in the SQL Server table, then it works fine, but I want to set up one field as the bit datatype in the SQL Server, and then input/edit data in the MS Access.

Does anybody have experience of it?




I've some problem when I connected to Sybase with ODBC and make a link table in MS Access 2003, Thai charset is not displayed correctly but It works in MS Access 97. Can anyone help me??




hi,

Currently i know how to extract info from 1 table in MS Access into an excel spreadsheet. However, when i do that, Ms excel will create a new spreadsheet(page) for that table. This is a good way to differentiate tables but for me i need a different method.


How do i extract the info i want into a single spreadsheet? This is because my tables are interlinked and i want to show the info across a single spreadsheet.




I can link tables to MS Access with no problems and have got use to the different fields held in Exchange and Access. Yet I am baffled why I can not see or pull in a date from MS Outlook Calendar. Can anybody help Thanks ........ Ian




Hi,

I have built a form without a record source as i need to mess around with the fields before i save.

On save, i would like the form to save to an ODBC linked table in my database.

I save the record through a save button which has the following code attached to its on_Click event.

' NOTE: DAO 3.6 code
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblODBCLinked", dbOpenTable)
With rs
.AddNew
.Fields("Country") = Me.txtCountry
.Fields("ID") = Me.ID
.Fields("Creation Date") = Now()
.Update
End With
rs.Close
Set rs = Nothing

I have no problem saving to a non-linked table with the above coding but always seems to fall over if as soon as i try to open the linked table. Ive debugged and the problem seems to be with opeing the table.

Set rs = db.OpenRecordset("tblODBCLinked", dbOpenTable)

Thanks in Advance for any suggestions

Access 97 / Windows NT / Full Read Write access. Not a permissions issue




hi

I have some problem in ms access forms.

i have a table named item master and item master form created using wizard.i am able to add records using the new record button.
now i have another table named rejection details that gets the item names from the item master.in a new form named rejection report i need to list all the item names from the item master and for each item i should key in the rejected quantity and save the entire data to rejection details.how the rejection report form should be designed? pls help




I tried to create my first table using sql in ms access but it failed.

My codes are like below. It was showing "Syntax error in field definition". What could be the problem??

Code: CREATE TABLE loom ( column1 integer, column2 integer, column3 integer, );




hi,

Currently i know how to extract info from 1 table in MS Access into an excel spreadsheet. However, when i do that, Ms excel will create a new spreadsheet(page) for that table.


How do i extract the info i want into a single spreadsheet? This is because my tables are interlinked and i want to show the info across a single spreadsheet.




When you have ODBC linked tables in an Access database is it possible to find out where the original tables are?




Hi all,

I am having a problem with this linked table(and the only table) in access which is linked to a database in MySQL via ODBC. the problem is that the linked table in access is read-only (I want it to be read and write).

I have searched the internet and found that you need to have the primary key defined, but I have already done that.

This is what I have done: I created the table in access and then exported the table to MySQL. I have then modified the table in MySQL as shown in attachment. I had to redefine the primary key, because Access did not. I then made a link back to access which now works except for it being a read only table.

Operating system: windows 2000
Office version: office 2000
MySQL version: mysql-4.1.7 for windows
ODBC driver version: MyODBC-3.51

I do not know What I have missed, and thought you smart people may be able to help me out.

Thanks very much for any help given.




Here's the set up:

I am the dbo of a SQL Server 2008 database, and we have an Access front end. I have 2 users that both have db_datareader rights to the SQL server db. They have the exact rights in SQL server 2008. User A linked a table from the SQL db in the Access front end, which I can open via ODBC. However, User B cannot open it. The error msg says "ODBC -- Connection to XXXX failed". User B then links a table, and I cannot open it, nor could User A. I then link a table, User A can open it, but not User B.

Is there some security setting in Access that can be causing this problem?




hi,
I have successfully bound the sub forms in MS access to the tables present in the database and run the application .

Now i wish to have a bigger database and thus shifted to oracle for database.Thus i created tables in oracle database and linked them to MS access using ODBC . But now my subforms are not working at all . They are not showing the data properly and they are not editable too . I can't even add a new row through the sub form now . Could anyone guide me as to what problem it could be ?

Note: the link table names and the previous access tables names are the same .




Limit On Tables And Views In MS Access (97)

I need to find out what the Limit is in Access when importing Data from Oracle or any other db. I've been told that there is a limit on how many tablesviews can bee seen when trying to import them.

This was my original question AND reply

I'm having problems importing tables or views into Access using Microsoft ODBC for Oracle. The driver version is 2.575.1022.0 and the MDAC version on my machine is 2.8.

I'm setting up the data source and using the Administrator login details.

If I connect to the oracle database via SQL+ I can see all the tables and views, likewise if I use another application i.e. Crystal.

I have checked on another users machine to make sure it's not anything to do with the MDAC build (they have 2.6) or the Microsoft Oracle Driver and I'm still getting the problem.

I have tried this from an NT machine and also an XP machine as we were not experiencing this problem before the rollout of XP.

Related to this problem is connecting via an ODBC data source to our Oracle Server via our NT server machine. I have checked the TNS entry on the server machine and can connect via SQL+.


Database Details
Oracle9i Release 9.2.0.3.0 - Production
Oracle 8.6 - Application Software

My machine details
Oracle client installs Oracle 8.6 and Oracle 9.2
Microsoft ODBC for Oracle (Driver Version 2.575.1022.0)

MDAC build 2.8
I've think I may have across this before, and the problem appears to be the admin login - where it (potentially) has far too many tables available, Access cannot see all of them and just returns one or two. I did work out some way round it before, but can't remember quite how at the moment! (not much help, I agree, but a good starting point!)
Hope somone can help !!