?

[danomaniac]

Greetings all, I have another question...

I need to have real-time data from my access database in my excel spreadsheet. I could do this by running a microsoft query in excel from a button in my spreadsheet, but I get an error saying I don't have permission when I try to connect to my access data source and I don't know the admin password and no one else does either.

Is there a way that I can create a link from an existing table to my spreadsheet?

 

[danomaniac]

Sorry about the vague topic, I typed my question and hit submit before I realized I hadn't entered a topic.

 

[ETID]

Sorry to say....but it sounds like you did all you can,...but without network permission (at the very least read access),your are skeeeerooooood.

 

[ETID]

Are these files access.mdb and excel.xls owned by you, and in a public location on the network?

 

[danomaniac]

By "owned," do you mean physically own them? Yes. And in a public location on the network. Yes. But it's a proprietary software package that runs over Access. I can do most anything from within the database, it just doesn't let me connect from outside.

 

[danomaniac]

Okay,
I'm almost there. After an hour on the phone with the software company's support people, I finally convinced them that I, being the DBA and the person responsible for making this place function, could not do so without the database username and password. "Never give up, Never surrender."

Now, I've created the query that accesses the database and boy, that works much better. Only hitch is, I'm having trouble passing the userid and password through vba. I don't want the user to have to enter it when prompted.

I read through the help on using the connection property, but haven't been able to get the syntax right. Any Ideas?

Thanks

Here's the code I'm using to run the query...

Sheets("Sheet1&quot.Select
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;Z:\pathtoqueries\query.dqy" _
, Destination:=Range("A1&quot)
.Name = "query_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

 

[danomaniac]

Ok. I feel stupid. I didn't have the Save Password box checked when I saved the query. Now, all I have to do is Refresh the Data. Works great.