I think it might not support it. Going from the source of the actual driver you see that it loads the params it needs to check, checks wether it has been given a DNS string, checks the ini files next and if it hasn't errored out it sets the params.
for reference from odbc.c latest mdb-tools (mdbtools-0.6pre1)
SQLRETURN SQL_API SQLDriverConnect(
SQLHDBC hdbc,
SQLHWND hwnd,
SQLCHAR FAR *szConnStrIn,
SQLSMALLINT cbConnStrIn,
SQLCHAR FAR *szConnStrOut,
SQLSMALLINT cbConnStrOutMax,
SQLSMALLINT FAR *pcbConnStrOut,
SQLUSMALLINT fDriverCompletion)
{
SQLCHAR FAR* dsn = NULL;
SQLCHAR FAR* database = NULL;
ConnectParams* params;
SQLRETURN ret;
TRACE("DriverConnect");
strcpy (lastError, "");
params = ((ODBCConnection*) hdbc)->params;
if (!(dsn = ExtractDSN (params, szConnStrIn)))
{
LogError ("Could not find DSN in connect string");
return SQL_ERROR;
}
else if (!LookupDSN (params, dsn))
{
LogError ("Could not find DSN in odbc.ini");
return SQL_ERROR;
}
else
{
SetConnectString (params, szConnStrIn);
if (!(database = GetConnectParam (params, "Database")))
{
LogError ("Could not find Database parameter");
return SQL_ERROR;
}
}
ret = do_connect (hdbc, database);
return ret;
then when you verify in connectparams.c, ExtractDSN specifically looks for the DSN= string
gchar* ExtractDSN (ConnectParams* params, const gchar* connectString)
{
char *p, *q, *s;
if (!params)
return NULL;
/*
* Position ourselves to the beginning of "DSN"
*/
p = strstr (connectString, "DSN");
if (!p) return NULL;
/*
* Position ourselves to the "="
*/
q = strchr (p, '=');
if (!q) return NULL;
And LookupDSN looks for inifiles or immediately returns with TRUE, depending on the HAVE_SQLGETPRIVATEPROFILESTRING precompiler setting.
So given that
SetConnectString (params, szConnStrIn);
only works on the data it got from the 2 previous functions, I think it doesn't support DSN-less. Only either a proper DSN= string or ini files.
The following code has served me well for years:
Function LinkTable(DbName As String, SrcTblName As String, _
Optional TblName As String = "", _
Optional ServerName As String = DEFAULT_SERVER_NAME, _
Optional DbFormat As String = "ODBC") As Boolean
Dim db As dao.Database
Dim TName As String, td As TableDef
On Error GoTo Err_LinkTable
If Len(TblName) = 0 Then
TName = SrcTblName
Else
TName = TblName
End If
'Do not overwrite local tables.'
If DCount("*", "msysObjects", "Type=1 AND Name=" & Qt(TName)) > 0 Then
MsgBox "There is already a local table named " & TName
Exit Function
End If
Set db = CurrentDb
'Drop any linked tables with this name'
If DCount("*", "msysObjects", "Type In (4,6,8) AND Name=" & Qt(TName)) > 0 Then
db.TableDefs.Delete TName
End If
With db
Set td = .CreateTableDef(TName)
td.Connect = BuildConnectString(DbFormat, ServerName, DbName)
td.SourceTableName = SrcTblName
.TableDefs.Append td
.TableDefs.Refresh
LinkTable = True
End With
Exit_LinkTable:
Exit Function
Err_LinkTable:
'Replace following line with call to error logging function'
MsgBox Err.Description
Resume Exit_LinkTable
End Function
Private Function BuildConnectString(DbFormat As String, _
ServerName As String, _
DbName As String, _
Optional SQLServerLogin As String = "", _
Optional SQLServerPassword As String = "") As String
Select Case DbFormat
Case "NativeClient10"
BuildConnectString = "ODBC;" & _
"Driver={SQL Server Native Client 10.0};" & _
"Server=" & ServerName & ";" & _
"Database=" & DbName & ";"
If Len(SQLServerLogin) > 0 Then
BuildConnectString = BuildConnectString & _
"Uid=" & SQLServerLogin & ";" & _
"Pwd=" & SQLServerPassword & ";"
Else
BuildConnectString = BuildConnectString & _
"Trusted_Connection=Yes;"
End If
Case "ADO"
If Len(ServerName) = 0 Then
BuildConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DbName & ";"
Else
BuildConnectString = "Provider=sqloledb;" & _
"Server=" & ServerName & ";" & _
"Database=" & DbName & ";"
If Len(SQLServerLogin) > 0 Then
BuildConnectString = BuildConnectString & _
"UserID=" & SQLServerLogin & ";" & _
"Password=" & SQLServerPassword & ";"
Else
BuildConnectString = BuildConnectString & _
"Integrated Security=SSPI;"
End If
End If
Case "ODBC"
BuildConnectString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=" & ServerName & ";" & _
"Database=" & DbName & ";"
If Len(SQLServerLogin) > 0 Then
BuildConnectString = BuildConnectString & _
"Uid=" & SQLServerLogin & ";" & _
"Pwd=" & SQLServerPassword & ";"
Else
BuildConnectString = BuildConnectString & _
"Trusted_Connection=Yes;"
End If
Case "MDB"
BuildConnectString = ";Database=" & DbName
End Select
End Function
Function Qt(Text As Variant) As String
Const QtMark As String = """"
If IsNull(Text) Or IsEmpty(Text) Then
Qt = "Null"
Else
Qt = QtMark & Replace(Text, QtMark, """""") & QtMark
End If
End Function
Best Answer
JDBC connection string shouls start with
jdbc:
like:so try with:
If you configure DSN then you can connect to it using simplier connect string:
jdbc:odbc:[alias]
, example: