How can I share an excel ODBC connection with other people?

1
votes

I recently developed an excel file that uses internal excel connections to update a few of the tabs from a data import tab within the workbook. Everything works great until I email it to one of my co-workers and he tries to update the data. He will get the error message below. I never set a password on the material so I am unsure why this error is occurring.

I cant add a photo (rep isn't high enough yet) but the error text is below.

Error Text Reenter Password The Password is missing or invalid for [name of file]. Please verify your Password and reenter it.

Connection string: Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\xxxxxx;Mode=Share Deny Write;Extended Properties="HDR=YES;";Jet OLEDB:System database=;Jet OLEDB:Registry Path=;Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

1
vbaexcelodbc
You didn't add the message. Also please show how the connection is defined in your code. – MatthewD
Oops, I forgot to add those. I updated the description with the message and connection details. – Mark T
What is xxxxxx ? Are you connecting to other workbooks or a database or something else? Here is info on the connection strings. vlsoftware.net/documentation/exportizer/… – MatthewD
I am using the xxxxxx as a placeholder for the actual location of the file on my computer. All of the data for the workbook is located within the spreadsheet in a static data import tab. I manually copy and paste data into this tab and the information then is shared with other tabs within the workbook. The issue is updating the other tabs in the workbook from the data import tab. – Mark T
So I'm not sure you need an ODBC connection. In general it is used to connect to other datasources. I know you can use it to load ranges and do queries against data in worksheets but that is not usually the easiest way to go about it. I would open another question with more of your code. Show what you are actually doing with the code and ask if there is an easier way that does not use an odbc connection. – MatthewD

1 Answers

0
votes

Just a guess: Since your Data Source points to a filepath, could it be that your co-worker doesnt have the file at the exact same path? Especially since that seems to live under "Users"? Maybe thats just Excel's way of telling you the file is missing?