[ODBC Driver Manager] Data source name not found and no default dr

[Guest]

Please Help… I have a button on a unbound form,
that fires a macro to run the code below… however…
I keep getting
[Microsoft] [ODBC Driver Manager] Data source name not found and no default
driver specified

I believe I have everything as far as Access installed correctly…
Is there a problem with my code?





Public Function Update_Assign() As String

'Error Handling
On Error GoTo Err_Update_Assign_Click


'Establish
Dim allRecords As ADODB.Recordset
Dim cnCh5 As ADODB.Connection
Dim strConnection As String

Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim MySQL As String
Set cnCh5 = New ADODB.Connection
cnCh5.Open strConnection

Dim prompt, buttons, title

Dim Doc_Type As Integer
Doc_Type = Forms!Assignment_frm!Type_ID

Dim Buyer As Integer
Buyer=Forms!Assignment_frm!Assign_Buyer

Dim C_Date As Date
C_Date = Date

Dim Comments As String
If IsNull(Forms!Assignment_frm!Comments.Value) = True Then
Comments = "N / A"
Else
Comments = Forms!Assignment_frm!Comments
End If


MySQL = "INSERT INTO Assignment_History(Type_ID, Doc_ID, Buyer,
Assigned_Date, Comments) " & _
"VALUES (" & _
"'" & Doc_Type & "', " & _
"'" & Doc_No & "', " & _
"'" & Buyer & "', " & _
"'" & C_Date & "', " & _
"'" & Comments & "', "

prompt = "Assignment History updated."
buttons = vbOKOnly
title = "Notification"
Update_Assign = MsgBox(prompt, buttons, title)


Set cmdCommand.ActiveConnection = cnCh5

'set the SQL statement to the command text
cmdCommand.CommandText = MySQL

'execute command
cmdCommand.Execute

Set allRecords.ActiveConnection = cnCh5
allRecords.Requery
Set allRecords.ActiveConnection = Nothing


Exit_Update_Assign_Click:
Exit Function

Err_Update_Assign_Click:
MsgBox Err.Description
Resume Exit_Update_Assign_Click

End Function

 

[Guest]

Hi Veritas,

You dim strConnection, then you try to use strConnection in your open
statement, but you don't actually set a value for strConnection before you
do. This is probably the start of your problem. Set a connection string
value before attempting to use it.

Hope this helps.

Damian.

 

[John W. Vinson]

I believe I have everything as far as Access installed correctly…
Is there a problem with my code?

Yes; you have a Dim strConnection as String, and an Open statement using it,
but nowhere do you define the contents of strConnection. Access has no way to
know what you're trying to connect to!

John W. Vinson [MVP]

 

[Guest]

Hi Damian,

Thank you for responding.

I am fairly new to VBA... and I pieced together the code below...
I just want to connect to the database i am currently in...

I have attempted to set strConnection =Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\My_Tracker.mdb;"

per my VBA books... and then I get the following...
The database has been placed in a state by user 'Admin' on machine ...
that prevents it from being opened or locked.

I tried taking off the... & "\My_Tracker.mdb;"
and then I get the following..
The Microsoft Jet database engine cannot open the file 'C:\Documents and
Settings\Veritas\Desktop\Databases. It is already opened exclusively by
another user, or you need permission to view its data.

Please advise.

Veritas

Hi Veritas,

You dim strConnection, then you try to use strConnection in your open
statement, but you don't actually set a value for strConnection before you
do. This is probably the start of your problem. Set a connection string
value before attempting to use it.

Hope this helps.

Damian.

Please Help… I have a button on a unbound form,
that fires a macro to run the code below… however…
I keep getting
[Microsoft] [ODBC Driver Manager] Data source name not found and no default
driver specified

I believe I have everything as far as Access installed correctly…
Is there a problem with my code?





Public Function Update_Assign() As String

'Error Handling
On Error GoTo Err_Update_Assign_Click


'Establish
Dim allRecords As ADODB.Recordset
Dim cnCh5 As ADODB.Connection
Dim strConnection As String

Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim MySQL As String
Set cnCh5 = New ADODB.Connection
cnCh5.Open strConnection

Dim prompt, buttons, title

Dim Doc_Type As Integer
Doc_Type = Forms!Assignment_frm!Type_ID

Dim Buyer As Integer
Buyer=Forms!Assignment_frm!Assign_Buyer

Dim C_Date As Date
C_Date = Date

Dim Comments As String
If IsNull(Forms!Assignment_frm!Comments.Value) = True Then
Comments = "N / A"
Else
Comments = Forms!Assignment_frm!Comments
End If


MySQL = "INSERT INTO Assignment_History(Type_ID, Doc_ID, Buyer,
Assigned_Date, Comments) " & _
"VALUES (" & _
"'" & Doc_Type & "', " & _
"'" & Doc_No & "', " & _
"'" & Buyer & "', " & _
"'" & C_Date & "', " & _
"'" & Comments & "', "

prompt = "Assignment History updated."
buttons = vbOKOnly
title = "Notification"
Update_Assign = MsgBox(prompt, buttons, title)


Set cmdCommand.ActiveConnection = cnCh5

'set the SQL statement to the command text
cmdCommand.CommandText = MySQL

'execute command
cmdCommand.Execute

Set allRecords.ActiveConnection = cnCh5
allRecords.Requery
Set allRecords.ActiveConnection = Nothing


Exit_Update_Assign_Click:
Exit Function

Err_Update_Assign_Click:
MsgBox Err.Description
Resume Exit_Update_Assign_Click

End Function

 

[Guest]

Please Note... my last post...

I just want to connect to the database i am currently in...

I have attempted to set strConnection =Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\My_Tracker.mdb;"

per my VBA books... and then I get the following...
The database has been placed in a state by user 'Admin' on machine ...
that prevents it from being opened or locked.

I tried taking off the... & "\My_Tracker.mdb;"
and then I get the following..
The Microsoft Jet database engine cannot open the file 'C:\Documents and
Settings\Veritas\Desktop\Databases. It is already opened exclusively by
another user, or you need permission to view its data.

Please advise.

Veritas

 

[John W. Vinson]

Please Note... my last post...

I just want to connect to the database i am currently in...

I have attempted to set strConnection =Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\My_Tracker.mdb;"

strConnection needs to be A TEXT STRING. You're trying to set it to an
expression

Provider=

Unless you have a variable named Provider, this makes no sense.

Shouldn't that be something like

strConnection =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path &
"\My_Tracker.mdb;"

all on one line of course, so that the value of strConnection becomes

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SomePath\My_Tracker.mdb;


John W. Vinson [MVP]

 

[Guest]

Sorry John,

Still not working...

strConnection needs to be A TEXT STRING. You're trying to set it to an
expression

Provider=

Unless you have a variable named Provider, this makes no sense.

Shouldn't that be something like

strConnection =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path &
"\My_Tracker.mdb;"

all on one line of course, so that the value of strConnection becomes

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SomePath\My_Tracker.mdb;


John W. Vinson [MVP]

 

[Guest]

Hi, I got it... I think...

Dim cnCh5 As ADODB.Connection
Set cnCh5 = CurrentProject.Connection

Dim cmdCommand As New ADODB.Command
Set cmdCommand.ActiveConnection = cnCh5

Dim MySQL As String

MySQL= "INSERT .... VALUES...


'set the SQL statement to the command text
cmdCommand.CommandText = MySQL
'execute command
cmdCommand.Execute

'Set myRecords.ActiveConnection = Nothing
Set cmdCommand.ActiveConnection = cnCh5
Set cmdCommand.ActiveConnection = Nothing

It is working anyway...

Thank you for taking the time to look at my issue... and help me work it out.

Sincerely,

Veritas

 

[John W. Vinson]

Hi, I got it... I think...

Good... Obviously the line

Set cmdCommand.ActiveConnection = cnCh5

was the key, since cnCh5 evidently exists.

John W. Vinson [MVP]