JiscMail Logo
Email discussion lists for the UK Education and Research communities

Help for ACCESS-VBA-DISCUSS Archives


ACCESS-VBA-DISCUSS Archives

ACCESS-VBA-DISCUSS Archives


ACCESS-VBA-DISCUSS@JISCMAIL.AC.UK


View:

Message:

[

First

|

Previous

|

Next

|

Last

]

By Topic:

[

First

|

Previous

|

Next

|

Last

]

By Author:

[

First

|

Previous

|

Next

|

Last

]

Font:

Proportional Font

LISTSERV Archives

LISTSERV Archives

ACCESS-VBA-DISCUSS Home

ACCESS-VBA-DISCUSS Home

ACCESS-VBA-DISCUSS  May 2002

ACCESS-VBA-DISCUSS May 2002

Options

Subscribe or Unsubscribe

Subscribe or Unsubscribe

Log In

Log In

Get Password

Get Password

Subject:

Re: ADO Commands

From:

Loughins Stephen <[log in to unmask]>

Reply-To:

Technical aspects of Access 97 and Visual Basic list <[log in to unmask]>

Date:

Tue, 7 May 2002 13:38:38 +0100

Content-Type:

text/plain

Parts/Attachments:

Parts/Attachments

text/plain (88 lines)

**********************************************************************

This document should only be read by those persons to whom
it is addressed,  and its contents are private and confidential.
If you receive this email message in error, notify the sender
immediately and do not disclose, copy or distribute this message,
or open any attachments.

**********************************************************************

The /x option looks good but I'm still a bit unsure as to how to reference
my AccessDb in code

My select from Oracle works OK but I need the code to perform the insert
into Access. I set an Access ODBC source but I got an error message relating
to a db that cannot be opened or locked. I've also tried using DAO, which
I've used before, but got a type mismatch error.

This is a sample of my code (I have been using either ADO or DAO to refer to
my Access db not a mixture as shown):

'ADO Declarations
Dim recOra As New ADODB.Recordset
Dim comAcc as New ADODB.Command
'DAO Declarations
Dim d As Database
Dim t As Recordset

Sub Insert
'Connection to Oracle
recOra.ActiveConnection = "ODBC;UID=specs;PWD=specs;dsn=DESDAT"
'ADO connection to Access - fails
comAcc.ActiveConnection = "provider=MSDASQL;DSN=Replicate"
'DAO Comands
Set d = CurrentDB
Set t = d.OpenRecordset("table",dbOpenDynaset) -- returns type mismatch
.
.
.
End Sub

Hope I'm not being too thick

Stephen


-----Original Message-----
From: Paul Winter [mailto:[log in to unmask]]
Sent: 07 May 2002 12:45
To: [log in to unmask]
Subject: Re: ADO Commands


----- Original Message -----
From: "Loughins Stephen" <[log in to unmask]>
To: <[log in to unmask]>
Sent: Tuesday, May 07, 2002 11:19 AM
Subject: ADO Commands
>
> I'm writing a procedure to select data from an Oracle database to populate
a
> table in Access2000. I've an ODBC source set up to perform the query but
I'm
> not sure how to reference the AccessDB to perform my inserts. Do I need to
> set up an ODBC source even though my code is within the AccessDB?
>

No.  If your code and (access) table are in the same database you need do
nothing.  If your code is in a Front-end and the tables in a Back-end
database then you'll need to the link the tables using either the Linked
Database Manager or code.

> Secondly, in Excel for example, if you open a file with a Workbook_Open
> event it runs code when the file is opened. Is there any way of doing the
> same thing with Access? The only way I've found to do it so far is to
attach
> my code to a form_activate event in a blank form, which is opened at
> startup.
>

I use the on-timer event of the start-up form when it's a data-entry system
or the /x macro - which Jason mentioned - when I have jobs scheduled to run
unattended.  The only thing I have in the macro is a call to a function
which does the work.

HTH

Paul

Top of Message | Previous Page | Permalink

JISCMail Tools


RSS Feeds and Sharing


Advanced Options


Archives

May 2011
April 2011
May 2008
April 2008
January 2008
October 2007
July 2006
September 2005
August 2005
March 2005
December 2004
July 2004
June 2004
April 2004
March 2004
September 2003
July 2003
June 2003
April 2003
March 2003
February 2003
January 2003
November 2002
August 2002
July 2002
June 2002
May 2002
March 2002
February 2002
January 2002
December 2001
November 2001
October 2001
September 2001
August 2001
July 2001
June 2001
May 2001
April 2001
March 2001
February 2001
January 2001
December 2000
November 2000
October 2000
September 2000
August 2000
July 2000
June 2000
May 2000
April 2000
March 2000


WWW.JISCMAIL.AC.UK

Secured by F-Secure Anti-Virus CataList Email List Search Powered by the LISTSERV Email List Manager