ODBC imported tables (1 Viewer)

Insomnai

Registered User.
Local time
Today, 23:15
Joined
Jan 16, 2009
Messages
14
Good afternoon! I haven't often posted if at all, but after much searching and failing I thought it best to ask the experts.

I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:

Code:
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MyDNSMachineName;UID=MyUID;PWD=MyPassword;LANGUAGE=us_english;" & "DATABASE=pubs", acTable, "INVOICE", "INVOICE"

I perform this several times in the same sub but I have noticed a bit of an oddity, even though one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.

Problem is I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.

Is anyone able to shed light onto why this might be happening?

Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?

Kindest regards

Craig
 
Last edited:

Insomnai

Registered User.
Local time
Today, 23:15
Joined
Jan 16, 2009
Messages
14
apologies for the bump, but has anyone got any advice for my two questions?

Kindest regards
 

RogerCooper

Registered User.
Local time
Today, 16:15
Joined
Jul 30, 2014
Messages
277
ODBC can be a bit flaky. Have you tried linking the tables instead of importing them? Then you would not need to refresh at all.
 

Insomnai

Registered User.
Local time
Today, 23:15
Joined
Jan 16, 2009
Messages
14
Hi and thank you for taking the time to respond. I have created a copy of the same database which runs from linked tables but sadly it is hideously slow at pulling data from Sage.
 

Insomnai

Registered User.
Local time
Today, 23:15
Joined
Jan 16, 2009
Messages
14
I can but that defeats the point of connecting to the ODBC and pulling a copy of the data directly into Access. I would rather not have extra steps inbetween...

Rather than exporting as a csv file, I have set up access to import directly into tables that I can use with various forms and queries but as my original post suggests, the problem is with one particular table only dragging (now 78) records into a table a few times and on another attempt brings in the whole set of records.
 

Users who are viewing this thread

Top Bottom