Problem with updating connection string to embedded MS query

I have an Access 2007 db that allows the user to combine data from various 
sources and then updates queries embedded in an Excel 2007 spreadsheet.  I 
update the connection string and the command text with the details of the 
path and database name and then refresh the queries, so it doesn't matter 
where the user puts the database or if they change the name.

This all works on my local machine, no matter where I place the db and Excel 
template.  However, over a network a type mismatch error is raised when the 
connection string is changed.  In the same environment one person can use it 
successfully on their local drive and another one gets the mismatch error.  
They both get the error over the network.  It's not a problem with the data 
because in each case they are pointing at the same files - there's no problem 
with importing the data into Access. 
 
I'm tempted to think this is a security issue but I can't quite work out why 
this is happening because it has been working up to now.  The db has been 
moved to a different folder, but the root folder and sub folders have been 
designated Trusted Locations so I'm not sure if that could cause the problem.

Here's an extract from the code where the error occurs:

Set db = CurrentDb

strConn = db.Name
    
strDir = Replace(strConn, "\" & Dir(strConn), "")

 xlWorkbook.Connections(intConn).ODBCConnection.Connection = Array(Array( _
            "ODBC;DSN=MS Access Database;DBQ=" & strConn & ";DefaultDir=" & 
strDir & ";DriverId=25;FIL=MS " _
            ), Array("Access;MaxBufferSize=2048;PageTimeout=5;"))
            

If I manually update the MS query with the connection string generated here 
it works and there is no problem.           

Can anyone suggest what I should be looking for?
0
Utf
11/26/2009 11:53:03 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

2 Replies
1250 Views

Similar Articles

[PageSpeed] 48

"BrainDead" <BrainDead@discussions.microsoft.com> wrote in message 
news:F360C612-7095-46C3-9EE0-EDD06E92E48F@microsoft.com...
>I have an Access 2007 db that allows the user to combine data from various
> sources and then updates queries embedded in an Excel 2007 spreadsheet.  I
> update the connection string and the command text with the details of the
> path and database name and then refresh the queries, so it doesn't matter
> where the user puts the database or if they change the name.
>
> This all works on my local machine, no matter where I place the db and 
> Excel
> template.  However, over a network a type mismatch error is raised when 
> the
> connection string is changed.  In the same environment one person can use 
> it
> successfully on their local drive and another one gets the mismatch error.
> They both get the error over the network.  It's not a problem with the 
> data
> because in each case they are pointing at the same files - there's no 
> problem
> with importing the data into Access.
>
> I'm tempted to think this is a security issue but I can't quite work out 
> why
> this is happening because it has been working up to now.  The db has been
> moved to a different folder, but the root folder and sub folders have been
> designated Trusted Locations so I'm not sure if that could cause the 
> problem.
>
> Here's an extract from the code where the error occurs:
>
> Set db = CurrentDb
>
> strConn = db.Name
>
> strDir = Replace(strConn, "\" & Dir(strConn), "")
>
> xlWorkbook.Connections(intConn).ODBCConnection.Connection = Array(Array( _
>            "ODBC;DSN=MS Access Database;DBQ=" & strConn & ";DefaultDir=" &
> strDir & ";DriverId=25;FIL=MS " _
>            ), Array("Access;MaxBufferSize=2048;PageTimeout=5;"))
>
>
> If I manually update the MS query with the connection string generated 
> here
> it works and there is no problem.
>
> Can anyone suggest what I should be looking for?

Are you sure this code will compile? It looks to me like you've a missing 
paren.

Array(Array(

has only one matching ) paren.


0
Stuart
11/27/2009 2:09:06 AM
"Stuart McCall" <smccall@myunrealbox.com> wrote in message 
news:OR9sgawbKHA.4780@TK2MSFTNGP04.phx.gbl...
> "BrainDead" <BrainDead@discussions.microsoft.com> wrote in message 
> news:F360C612-7095-46C3-9EE0-EDD06E92E48F@microsoft.com...
>>I have an Access 2007 db that allows the user to combine data from various
>> sources and then updates queries embedded in an Excel 2007 spreadsheet. 
>> I
>> update the connection string and the command text with the details of the
>> path and database name and then refresh the queries, so it doesn't matter
>> where the user puts the database or if they change the name.
>>
>> This all works on my local machine, no matter where I place the db and 
>> Excel
>> template.  However, over a network a type mismatch error is raised when 
>> the
>> connection string is changed.  In the same environment one person can use 
>> it
>> successfully on their local drive and another one gets the mismatch 
>> error.
>> They both get the error over the network.  It's not a problem with the 
>> data
>> because in each case they are pointing at the same files - there's no 
>> problem
>> with importing the data into Access.
>>
>> I'm tempted to think this is a security issue but I can't quite work out 
>> why
>> this is happening because it has been working up to now.  The db has been
>> moved to a different folder, but the root folder and sub folders have 
>> been
>> designated Trusted Locations so I'm not sure if that could cause the 
>> problem.
>>
>> Here's an extract from the code where the error occurs:
>>
>> Set db = CurrentDb
>>
>> strConn = db.Name
>>
>> strDir = Replace(strConn, "\" & Dir(strConn), "")
>>
>> xlWorkbook.Connections(intConn).ODBCConnection.Connection = 
>> Array(Array( _
>>            "ODBC;DSN=MS Access Database;DBQ=" & strConn & ";DefaultDir=" 
>> &
>> strDir & ";DriverId=25;FIL=MS " _
>>            ), Array("Access;MaxBufferSize=2048;PageTimeout=5;"))
>>
>>
>> If I manually update the MS query with the connection string generated 
>> here
>> it works and there is no problem.
>>
>> Can anyone suggest what I should be looking for?
>
> Are you sure this code will compile? It looks to me like you've a missing 
> paren.
>
> Array(Array(
>
> has only one matching ) paren.


Sorry. Scratch that. I should've looked more carefully before posting.

Can't help with the ODBCConnection. I've never had occasion to work with it.


0
Stuart
11/27/2009 2:14:14 AM
Reply:

Similar Artilces: