Problem opening shared Access through excel

[udigold1]

Hi,
I have an Access DB on a network folder. I have an Excel file that connects to the DB using vba code.
Problem is whenever I'm opening the DB through Access, when other users trying to connect to the DB via excel they're getting the "data link properties" dialog and can't go on.
How can I solve it?

here's the vba code in excel:

[COLOR=#333333]
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\na02\data$\bakara\gemel\datain\DayDataIn1.accdb;Mode=Share Deny Write " _
        , _
        ";Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:En" _
        , _
        "gine Type=6;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" _[/COLOR]

Thanks,
Udi

 

[CalcSux78]

Is it an issue with your connection string? I've used something along these lines for years and has worked well.

Sub OpenTable()
Dim rs As Object, Conn$, str$


Set rs = CreateObject("ADODB.Recordset")
str = "\\na02\data$\bakara\gemel\datain\DayDataIn1.accdb;"
Conn = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" & str


rs.Open Source:="Table1", Conn:=cn, CursorType:=2, LockType:=3
Range("A1").CopyFromRecordset rs, MaxColums:=1
End Sub