RE: To Automate an ODBC tables link via a button on switchboard
anonymous_at_discussions.microsoft.com
Date: 10/15/04
- Next message: Sandy: ".snp file conversion"
- Previous message: Jeff Boyce: "Re: A database of demographic data about 1000 people"
- In reply to: BerHav: "RE: To Automate an ODBC tables link via a button on switchboard"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 15 Oct 2004 06:30:47 -0700
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.
>
>"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
>>
>>
>.
>
- Next message: Sandy: ".snp file conversion"
- Previous message: Jeff Boyce: "Re: A database of demographic data about 1000 people"
- In reply to: BerHav: "RE: To Automate an ODBC tables link via a button on switchboard"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
