Connection string issues

[jelybeans]

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

 

[AccessVandal via AccessMonster.com]

I'm not sure that this will help. Add one more line below the Dim

Dim Cnxn as ADODB.Connection

and from "Set setCnxn = Cnxn" to "setCnxn = Cnxn".

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

 

[AccessVandal via AccessMonster.com]

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.

 

[Stefan Hoffmann]

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 <--

 

[jelybeans]

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

 

[jelybeans]

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

 

[AccessVandal via AccessMonster.com]

What is means is that a user had opened this database or you the "User" had
opened this database and is attempting to re-open again with this code on the
same database.

 

[jelybeans]

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.

 

[AccessVandal via AccessMonster.com]

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.

 

[v_fas]

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