RE: To Automate an ODBC tables link via a button on switchboard

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: BerHav (berhavAThavebeDOTde_at_GuessWhat-No.Spam)
Date: 10/13/04 

Date: Wed, 13 Oct 2004 05:55:03 -0700

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.

"Colin Auton" wrote:

> 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
>
>

Relevant Pages

  • Re: Date range on reports
    ... > box to your report with a control source like: ... >> In the Database window (Database window: The window that appears when you ... >> In the New Form dialog box, click Design View, and click OK. ... >> Begin by clicking Macro Names to display the Macro Name column. ...
    (microsoft.public.access.reports)
  • RE: To Automate an ODBC tables link via a button on switchboard
    ... have you linked the tables from your Sage Line ... database into your ... Create a new Macro, ... >> Colin Auton ...
    (microsoft.public.access.externaldata)
  • Re: Newbies first image: BSOD 0x7B
    ... Having all the components mentioned in the macro platform component would be ... > - add this component to the database using the Component Database Manager, ... > Info:Creating: LSI Logic FC909 Fibre Channel Adapter ...
    (microsoft.public.windowsxp.embedded)
  • RE: Macro to VBA
    ... intLogonAttempts = intLogonAttempts + 1 ... MsgBox "You do not have access to this database. ... Database Administrator ... from the macro that does the checks and opens the form if everything passes. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Manipulating MS Access records with excel VBA + ADO
    ... and other SQL filters into the code. ... Then in the 1st macro ... You have the choice of searching through the database by making a SQL to ... ' Macro recorded 1/19/2009 by Joel ...
    (microsoft.public.excel.programming)