To Automate an ODBC tables link via a button on switchboard

[Colin Auton]

Hi

I would be very grateful if anyone could help me. I have
posted this on several Forums and no-one seems interested
in assisting. I do not think it is too technical for
anyone who knows Visual Basic.

A Brief insight into the problem. I did have my Access
database tables linked through ODBC to 4 live tables
within a networked version of Sage Line 50 Accountancy
software.
The data in this program is huge and took up to 5 hrs to
run the most complex of reports.
Since then the 4 tables are now imported into Access on a
daily basis which takes about 20mins and now the most
complex reports takes about 30seconds to run. This is
acceptable.

Problem.
I want to automate this by clicking a button through the
switchboard. To take away the end user having to go behind
the scenes of the switchboard.
It needs to:
1) Delete the 4 existing tables (INVOICE, INVOICE_ITEM,
SALES_LEDGER, STOCK) saying YES to the 4 popups when
trying to delete
2)New, Import Table,Ok, Drop down "file of types" to ODBC
Databases()
3)Choose Machine Data Source,Sageline50v7,ok,Log on name =
manager, password = password,ok
4)Select INVOICE, INVOICE_ITEM, SALES_LEDGER, STOCK,OK
5)Return back to menu screen when completed.

I have had a go at trying to write the makro for this then
assign it to a button but it does not work.

Can anyone assist please?

If you are not able to assist please could you guide me in
a direction where i might be able to get help.

Thank you very much
Colin Auton

 

[Guest]

Hi Colin,

Question: have you linked the tables from your Sage Line database into your
Access database? Will the password change frequently (every month, every 3
months, etc)? If the password won't change you could tell Access to store
UserID and Password permanent during the link process of each table.

1) If you want to use a macro, create for each table a Delete Query (deletes
the data in your tables, but not the table itself). Create a new Macro,
insert in the first line SetWarnings : No, Start the 4 Delete Queries with
OpenQuery.
2) not necessary if tables are linked permanent into Access and password and
user is stored
3) not necessary - see above
4) Create 4 Append Queries, sources are the linked tables. Add OpenQuery for
each to the macro above.

The SetWarning command will suppress all messages from Access, therefore you
won't see a message like "Do you really want to delete ..." or "Your are
adding 20000 rows to table 'abc' ".
If you like you can add a SetWarning : Yes at the end of the Macro. But it
is not needed, as the Warnings will be enabled again by Access at the end of
a macro.

HTH
Bernd

BTW: most of the people answering questions are not working for Microsoft
and not getting paid for it and do this as a hobby. So if you claiming
'no-one seems interested in assisting' think of that.

 

[Guest]

Fabulous advice. Worked a treat. Thank you.

-----Original Message-----
Hi Colin,

Question: have you linked the tables from your Sage Line database into your
Access database? Will the password change frequently (every month, every 3
months, etc)? If the password won't change you could tell Access to store
UserID and Password permanent during the link process of each table.

1) If you want to use a macro, create for each table a Delete Query (deletes
the data in your tables, but not the table itself). Create a new Macro,
insert in the first line SetWarnings : No, Start the 4 Delete Queries with
OpenQuery.
2) not necessary if tables are linked permanent into Access and password and
user is stored
3) not necessary - see above
4) Create 4 Append Queries, sources are the linked tables. Add OpenQuery for
each to the macro above.

The SetWarning command will suppress all messages from Access, therefore you
won't see a message like "Do you really want to delete ..." or "Your are
adding 20000 rows to table 'abc' ".
If you like you can add a SetWarning : Yes at the end of the Macro. But it
is not needed, as the Warnings will be enabled again by Access at the end of
a macro.

HTH
Bernd

BTW: most of the people answering questions are not working for Microsoft
and not getting paid for it and do this as a hobby. So if you claiming
'no-one seems interested in assisting' think of that.


.