ever hit a brick wall (Connect to SQL Server from Excel via Access)

[snakeyes2002]

hope someone is there that can provide me with an answer

at work, I am now responsible for developing 'live' reports in Microsoft Excel from the data in a commercial database system that uses SQL Server

The first step I done was to create a Microsoft Access Database that links to EVERY table in SQL Server (over 200) - i don't need all of them - its just the result i got when i coded how to relink all tables at startup of the database.

Anyway, in Microsoft Excel 2002, I have coded a complex pack of reports that:

1) Use DAO (i know - old stuff, but i know it better that ADO) to open my database
2) Create specific queries (concancenation(did I spell that right?) of user criteria in an excel userform.
3) Execute these queries
4) For each field in each record move and place each fields value in a respective cell (took me AGES to work this out)
5) Do a lot of formatting with the data in Excel to provide a customised and filtered report.

All the code, queries and connections work correctly, however, I am (the user) prompted with SQL Server Login Dialog every time I execute a query. In some instances I am prompted 12 times in a short time (12 different queries)

How do i suppress this dialog, or make it appear only once?

 

[Sergeant]

Disclaimer: I am not a SQL Server guru, nor do I play one on T.V.

It occurs to me that the problem may be in the way that your code connected the db to the SQL tables. (How did you do that?)
Can you open these linked tables in your access db or project without entering SQL Server credentials?

I just tried this: (assuming all tables are in one SQL database)
-Open an access.adp file and click File/Get External Data/Link Tables.
-Select Linked Server and click next.
-In 'Select Data Source', under 'My Data Sources', click +new SQL server connection.
-'Data Connection Wizard' appears...type SERVERNAME\SQLNAME and select 'Use Windows Authentication' click next.
-A list of databases appears, select one and a list of tables appears. You can select all the tables in the db. They are all listed under Queries in the project.

Sarge.

 

[snakeyes2002]

The code in my mdb, the Relinker() to be specific took me months to create. On Startup, the Relinker code runs by:

1) checking the linked tables in the database
2) deleting all linked tables
3) look at the windows xp registry to check username and password for authentication (the password is the same on everybody pc, however this could change and i coded for it - just in case)
4) creates links to all the tables in the SQL server

within the database after the relinker has run, I can (stress that one) access for read/write every table and query in SQL server without the need to enter a username and password.

I created programs in Microsoft Excel so that all data to the average user would be unlinked to SQL server, hence prtecting the trust placed in me accessing the system (500 Million records more or less - for read/write!)

How do I store the sql server conn string in excel vba so i can run a query without prompts?

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase(stDatabase)
With db
.CreateQueryDef "Temp", stSQL
.QueryDefs.Refresh

so far okay

Set rs = .OpenRecordset("Temp")

WHAM - username and password

anybody plz

 

[Sergeant]

Didn't really soak in your reply yet, gotta run out fer lick'r. While I'm out, think about this...

Is it possible to build the worksheet data using a query in Access?
Then you could Export it to the appropriate xl template sheet.

Sarge.

 

[snakeyes2002]

during my research into the ways that the data can be used, and from what application a few ideas came to mind

i thought about and actually designed a system that would use Access to export and manipulate Excel in the same way, however, due to the amount of information and the sensitivity of some data, i decided against using Access.
I wouldn't want some1 creating an action query and executing it - i do trust the users - but i can prevent it.

I understand that i could design a secure form and even an mde, but again, that seems like too much (the forms in Excel are HUGE) (30+ check boxes, 8 listboxes, 8 comboboxes - maybe a bit much

 

[Sergeant]

OK, I'm beginning to realize that your users are going to get the data on a DIY basis, is that right?

Off the top of my head:
Use a native access make-table query to put the data into "temp tables" and then access those to fill the xl sheet(s) via ADO/DAO. Everytime you run the queries for new data, the previous temp table would be overwritten.

Am I way off base here?

Sarge.

 

[snakeyes2002]

make table queries?

i have attempted this in the past with other database systems and i understand how it works.

i will attempt it in work today and update. Before trying, i think SQL server will ask for a password anyway, given the track record.

i will also bring back most of the code i use at startup and during the queries

 

[snakeyes2002]

wot i am trying to do may require me to use ADO 2.5 i think, rather than DAO to connect to SQL server.

ADO, i think, has better options when extracting data and for entering passwords, however, I am still stuck as to how to specify a userid and password automatically without prompting the user.

i attempted to do as you suggested and create a make-table query to enable all records i wish downloaded to be placed in a temporary table - this still prompted me with a password.

Looks as if I am not going to find a solution and may have to consider rewriting the entire program from scratch in ADO (i cannot use it that great)

 

[snakeyes2002]

Been a few days

I'm back.

I decided after a lot of further research into why I need to connect using ADo/DAO and why I need to use Excel.

I created a new Microsoft Access Database with a connection to SQL Server - as in the relinker code before. Except I now connect READONLY! It was just that simple.

I have designed an mde front end that has a few forms only. The form sends parameters to a back-end database (actually on an ad-hoc mapped drive) that contains the datasources, connections, queries and reports including all my precious code.

Now, next is automating Microsoft Excel in great depth...

Thanks for the guidance