Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 14th, 2009, 11:38 AM
Authorized User
 
Join Date: Jan 2009
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Default Access query

I have an access database for a client and it interfaces into another data source. I have an excel program (this is what the clients wishes to use) that is executed nightly in which my VBA code executes a multitude of apend and update query's. These query's access the additional data source and requires a user id and password to be entered.

The current process is as follows: User opens excel program and a series of events automatically begin. Once is reaches a certain point, it executes an append query that interfaces with the additional data source. The user then has to enter his/her id and password. At that point, the program stalls and continues at 3AM once all processing has been completed in the additional data source.

Now, client doesn't want to have a user involved in this first part. They wish for the program to open automatically and run on it's own.

My problem:

I don't know how to write the VBA code so that it executes the query and set's the UserID and Password. I have been researching on my own for a couple of days now. I've read over and over to stay away from SendKeys method. I've been able to find how to set the connection string for database id/password but not at the query level. Can anyone provide some suggestions or help sites that can help me learn how to accomplish this?
 
Old September 14th, 2009, 06:10 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Not sure I can help, but I think we'd all have a better shot at doing so if you would show the code you are using now.

For starters, how do you open the connection to the "additional data source"??
The Following User Says Thank You to Old Pedant For This Useful Post:
SheriV (September 15th, 2009)
 
Old September 15th, 2009, 09:12 AM
Authorized User
 
Join Date: Jan 2009
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Default

My Access database has a linked table to the datasource. The additional datasource has it's own ODBC connection. The datasource name is Velocity. It was developed by a company called AFS. It's very similar to an access database. I think I have found the connection string to use if I was going to use MS query to query against it. This may be of help once I figure things out but for now not sure how it will come into play. My query is in Access and it is an append query. This query retreives information from the linked table and apends to a local table in my database. However, the first time this query is executed, it establishes the connection to the datasource and requires a user id an password. Right now, I'm simply using DAO code that opens the database and peforms dba.execute against the query. A dialog box then appears asking for the user id/pwd. (SQL Servier Login).The datasource is responsible for this not my code.

My boss wanted me to investigate SendKeys and use that but after all the research I did on that, I told him no. It is just too tricky and not a reliable way to go. I would rather spend my time researching another way that would be more dependable and which I could learn something of value that could be used in future projects. I've been programming using access/excel for about three years now but the past couple of months I have only begun to work with linked tables to another ODBC datasource. I'm learning on the fly here and having to teach myself.

I am familiar with code to open Access and provide the id/password when required. But not at the query level. Maybe I should try to figure out a way to open the datasource after access is opened to establish the connection and is there a way to do this using code to connect and provide the id/pwd similar to that of the open code for Access?.....


Last edited by SheriV; September 15th, 2009 at 09:43 AM..
 
Old September 15th, 2009, 10:29 AM
Authorized User
 
Join Date: Jan 2009
Posts: 20
Thanks: 2
Thanked 0 Times in 0 Posts
Default

This turned out to be much more simple that I thought.....When I link the table, I can enter and save the password which eliminates the prompt.... I think this just solved my problem. Have to do some testing but I think this is the simplest answer....





Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with access query jrobertson Access VBA 6 January 25th, 2007 05:49 PM
Access Query gre_smi SQL Server 2000 2 January 11th, 2006 08:59 AM
need access query kale_tushar Access 2 January 3rd, 2005 05:26 AM
Eomonth in Access Query Mihai B Access VBA 0 May 26th, 2004 06:15 AM
Access: need a Query. please help me... alienscript Access VBA 1 December 14th, 2003 01:02 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.