Can someone help me troubleshoot my connection string? I'm using Access 2007.

Here is my current code:

Function setCnxn() As ADODB.Connection

Dim cnxnStr As String

Set Cnxn = New ADODB.Connection

cnxnStr = cnxnStr = "Data Source=filepath.accdb;"

With Cnxn
.Mode = adModeShareDenyNone
.CursorLocation = adUseClient
.Open cnxnStr
End With

Set setCnxn = Cnxn

End Function

Here are some other strings I've tried:

cnxnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filepath.accdb;"

cnxnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Driver={Microsoft Access
Driver (*.mdb, *.accdb)};DBQ=C:filepath.accdb;Persist Security Info=False;"

cnxnStr = "Driver={Microsoft Access Driver (*.mdb,
*.accdb)};DBQ=C:\Users\Summer\Desktop\PMOBlotterDB\PMOBlotter.accdb; Persist
Security Info=False;"

I've read the ConnectionString.com site and hat's where I got my one of the
strings but it doesn't work either.

I've gotten several different error messages for each of the strings I've
tried. With the current string i'm getting:

Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Driver Manager] Data source name too long.

Please Help

jelybeans schrieb:
> Can someone help me troubleshoot my connection string? I'm using Access 2007.
> Here is my current code:
> cnxnStr = cnxnStr = "Data Source=filepath.accdb;"
This is the problem:

This line evaluates the comparison and returns a boolean converted to
string.
It's the same as

cnxnStr = (cnxnStr = "Data Source=filepath.accdb;")
MsgBox cnxnStr

btw, for connection strings see:

http://connectionstrings.com/access-2007


mfG
--> stefan <--

I took the file path out and replaced it with "filepath" so the name on the
post wouldn't be so long. I have the correct path name with the file name in
the program. Thanks for trying to help
--
Piper


"AccessVandal via AccessMonster.com" wrote:

> Please ignore the "Set setCnxn = Cnxn". Wasn't thinking.
>
> This should work, change from this
> "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filepath.accdb;"
>
> To this
> "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.
> accdb;Persist Security Info=False;"
>
> "C:\myFolder\myAccess2007file.accdb" is where your file is located not
> "filepath.accdb".
>
> Watch for word wrap in your browser.
>
> jelybeans wrote:
> >Can someone help me troubleshoot my connection string? I'm using Access 2007.
>
> --
> Please Rate the posting if helps you.
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200903/1
>
>

I'm sorry the following was a typo on my part:

cnxnStr = cnxnStr = "Data Source=C:\filepath.accdb"

my code actually reads:

cnxnStr = "Data Source=C:\filepath.accdb"

I've been to connectionstrings.com, that;s where I got the following string:

cnxnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=filepath.accdb;Persist Security Info=False;"

When I use this string it gives me the following error:

Runtime error '-2147467259 (80004005)':

The Database has been placed in a state by user "Admin" on machine
'SUMMER-PC' that prevents it from being opened or locked.

I don't know why it does this, I'm asumming it has something to do with the
connection string because the program runs fine when I comment the string
out. I've looked extensively on Connectionstrings.com for another string but
I can't find any other than the one that gives this error.

Thanks for the help - sorry for the typo
--
Piper


"Stefan Hoffmann" wrote:

> jelybeans schrieb:
> > Can someone help me troubleshoot my connection string? I'm using Access 2007.
> > Here is my current code:
> > cnxnStr = cnxnStr = "Data Source=filepath.accdb;"
> This is the problem:
>
> This line evaluates the comparison and returns a boolean converted to
> string.
> It's the same as
>
> cnxnStr = (cnxnStr = "Data Source=filepath.accdb;")
> MsgBox cnxnStr
>
> btw, for connection strings see:
>
> http://connectionstrings.com/access-2007
>
>
> mfG
> --> stefan <--
>

Can you tell me what I need to do to fix this? I open the program, open the
form and make a selection in the combobox (which is what then starts to run
the code). I've used connection strings in Access 2003 extensively but have
been unable to connect using access 2007.

Thanks for the help.
--
Piper

What do you change the line to?
Set Cnxn = CurrentProject.Connection ‘change this


"AccessVandal via AccessMonster.com" wrote:

> If you’re opening the current database connection, try this
>
> Function setCnxn() As ADODB.Connection
>
> Dim cnxnStr As String
> Dim Cnxn as ADODB.Connection
>
> Set Cnxn = CurrentProject.Connection ‘change this
>
> cnxnStr = ""Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\
> myAccess2007file.
> accdb;Persist Security Info=False;"
>
> With Cnxn
> .Mode = adModeShareDenyNone
> .CursorLocation = adUseClient
> .Open cnxnStr
> End With
>
> Set setCnxn = Cnxn
>
> End Function
>
> Watch for browser word wrap.
>
>
> jelybeans wrote:
> >Can you tell me what I need to do to fix this? I open the program, open the
> >form and make a selection in the combobox (which is what then starts to run
> >the code). I've used connection strings in Access 2003 extensively but have
> >been unable to connect using access 2007.
> >
> >Thanks for the help.
>
> --
> Please Rate the posting if helps you.
>
> Message posted via http://www.accessmonster.com
>
>