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/


Post your answer or comment

comments powered by Disqus
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 !!

Dear all,

Just want to share the solution I built for creating of high-quality HTML reports in MS Access, describe workarounds, which I had to implement as well as to provide a link to real tool which implements this solution for MS Access 2007 / 2010 (Meeting minutes, Issues, Risks, Agreements, Actions, Projects Tracking Tool).

There is nothing extremely new in my solution; similar approach is described previously

However I give a link to real implementation as well as describe workarounds, which I had to use.

Problem description:

This is a common knowledge, that even though available, the export to HTML from MS Access report is not working well. Such export will never produce a high-quality HTML
So, the only solution to save Access report outside of MS Access is to PDF it. This solution has disadvantages:
It is difficult to edit or comment PDF or to copy and paste information from it. afterwards. Hence it is difficult to others either to use data from this report or to provide a feedback on it. If you send it via email, you have to send it as attachment. Now days, when everybody is using mobile phones to read emails, it creates extra complications. Besides, if you email information as attachment, you can’t use MS Outlook search engine to search for information within attachment.

Solution

Even though MS Access can’t export reports to HTML well, the structure of Access report is very suitable to create an HTML code itself.

The code of HTML page has header, body and footer and MS Access report has the same elements.

Table in HTML has also sort of header body and footer, hence it is very easy to create HTML code for table using subreports: e.g.

Header:





Header 1
Header 2


Body


cell 1
cell 2


Footer


So, the trick is to create report which, when saved as text format, would create HTML code.

As example, if you want to have a header at the top of HTML report, you have to create the following text in MS Access report:

Header text

Tricks, issues and workarounds
There are several tricks though, which one has to use.

1) Use CSS
Use CSS in your HTML code, it will make the HTML code much compacter.

2) Rich formatted Memo field
In Access 2007 there is a nice feature of rich format for Memo field, which is in reality saved as html text in Access. It is very well compatible with our approach. So, when placing control on MS Access report for this rich-formatted memo field, just set it’s property to text (not rich text), this will produce a direct HTML code into your report for rich-formatted memo field.


3) Issue, when exporting to text.
There is an issue, that export of complex reports (with subreports and a lot of information etc) to text formal does not work well always, there seems to be a bug in Access. Sometimes lines get mixed, sometimes information gets dropped completely.

Workaround.
I found, that exporting to rich format works much better. So the trick is to export Access report 1st to rich format and then use office automation of MS Word from MS Access to open this .*rtf file and save it to text format with *.html extension. It reality it works quite well and user does not notice anything as MS Word opens in background.

You can find MS Access 2007/2010 database, which uses this approach to produce HTML reports in sourceforge if you search for the word "MIRAAPT" in Google.

Note: MIRAAPT stands for Meeting Minutes, Issues, Risks, Agreements, Actions, and Projects Tracking.

Regards.

Evgeny.

P.S. by the way, if somebody is interested in my tool and wants to co-develop – you are more than welcome to get in contact with me.

I recently installed MySQL ODBC 5.1 Driver, but my MS Access files are still trying to use version 3.51, giving a generic error - somethings like ODBC – connection to ‘{MySQL ODBC 3.51 Driver} failed.

The only way I can fix them is to re-link the tables back in MS Access which is very time consuming.

Is there a quick fix to getting all MS Access databases to use the latest driver? Hope you can help.

I have access 2007 which I have created a DNS to SQL server hosted somewhere on the internet. I need to create a query in access to modify the data on the server side through linked tables, and send this access file to my client to make the changes. Therefore the linked tables have to remain "linked" when the client uses the query.

I already created the ODBC and named it a specific DNS, and I can see the data fine. I want to somehow keep this link intact when I give the file to the client so that they don't have to create a ODBC connection.

Is there a way to employ this connection embedded into the file? Do I have to use Visual Basic Code somehow?

Any help would be greatly appreciated.

I have a problem on how to Update and Append data on Linked tables while Using access 2003 as the front end and SQL Server 2000 as the back end linked via an ODBC,

Any time I try I receive an Error

It must be an unpalatable Query

Help please

Guys,

I have a problem I can't understand. You may have already experience this.

We have in our company Access db that were designed in version lower than our current (access 2007).
In some of those access db, users used some linked tables (through ODBC connection on MS SQL).

We needed to change their ODBC so that point to another database (same scheme = views instead of tables). We did some extensive tests and everything worked fine.

Now from time to time (I was not able to find any pattern), a query that use N linked tables (and NO access tables) returns funky data. It seems that the Engine that pass the query to SQL (the Jet engine I guess) generates stupid SQL statements and as a results multiply by x the records or miss some records.

I did a compact and repair. Still.

I had to delete the linked tables and recreate the link (and did not specifiy 'unique record identifier').
then the resulys were correct.

Any idea why ? Can it be a pb from 2007 ?

Thank you in advance


Not finding an answer? Try a Google search.