Serious Problem - Connecting to a Text Database

[Suman]

Is there any body out there to help me.. I have a Problem in Connecting to a Text Database Using ODBC


The Cooection String I used is

"Provider=MSDASQL;Driver={Microsoft Text Driver(*.txt;*.csv)};DBQ=E:\D999\;"


where I have all my text files in my E:\D999\ directory.

But when I tried to Open the Connection
it says "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

Please help me to Solve this ,its Very Urgent ...

Hi Suman, are u sure u created a DSN (Data Source Name) using the ODBC32 bit. You can go to the control panel and click on the ODBC32 and then create a new text DSN and use it in ur connection string. I can try and send u the code if u wish so. Please mail to gantibabu@yahoo.com. All the best...vijay

[Suman]

Thanks for that reply . But the Problem is that I dont want to create a DSN manually . I want to connect to the Text Database using a DSNless Connection .

When I am using the DSN its Connecting to the Database .But the application which I am creating is gonna be distributed one . So I cant expect the users to create a DSN on each PC's . Thats why I want a DSN less Connection .

Is there a way to Connect to Text Database using OLEDB without using the ODBC ..


Please Help me ..

[gparreira]

Dear Suman,

As you can see in http://support.microsoft.com/support/kb/articles/Q174/6/55.ASP the driver name must match exactly the name as it is installed in your computer. Have you checked that???

Good luck,

gparreira

[Suman]

I checked that and I think the odbcjt32.dll or mstext40.dll version seems to be different than earlier coz I had installed MDAC 2.5 2 days back . So at last I had to write it in a different way .. Create a DSN programmatically . If DSN already exist then modify the DSN ...

I am writing the Code for that here ... May be useful for some one ...

Put the Following Code in a Module


'-------Module Starts-------------

Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
Private Const HKEY_LOCAL_MACHINE = &H80000002
Public Const HKEY_CURRENT_USER = &H80000001
Private Const ERROR_SUCCESS = 0&
Private Const KEY_QUERY_VALUE = &H1
Private Const KEY_NOTIFY = &H10
Private Const READ_CONTROL = &H20000
Private Const KEY_ENUMERATE_SUB_KEYS = &H8
Private Const STANDARD_RIGHTS_READ = (READ_CONTROL)
Private Const SYNCHRONIZE = &H100000
Private Const KEY_READ = ((STANDARD_RIGHTS_READ Or KEY_QUERY_VALUE Or KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY) And (Not SYNCHRONIZE))
Public Enum enumDSNType
eUser = 1
eSystem = 2
End Enum

Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" (ByVal hwndParent As Long, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long

Private Const ODBC_ADD_SYS_DSN = 4 ' Add data source
Private Const ODBC_CONFIG_SYS_DSN = 5 ' Configure (edit) data source

Public Function IsDSNExist(pDSNName As String, pType As enumDSNType) As Boolean
Dim lKeyHandle As Long
Dim lRet As Long
Dim strBuffer As String
Dim strKey As String
Dim strValue As String

strKey = "SOFTWARE\ODBC\ODBC.INI\" & pDSNName

If pType = eSystem Then
lRet = RegOpenKeyEx(HKEY_LOCAL_MACHINE, strKey, 0, KEY_READ, lKeyHandle)
Else
lRet = RegOpenKeyEx(HKEY_CURRENT_USER, strKey, 0, KEY_READ, lKeyHandle)
End If
If lRet = ERROR_SUCCESS Then
IsDSNExist = True
RegCloseKey lKeyHandle
End If
End Function
Public Function CreateDSN(DSNName As String, StrPath As String, Add As Boolean) As Boolean
On Error GoTo errdsn
CreateDSN = True

Dim StrAttr As String
Dim ErrObj As Error



StrAttr = "CHARACTERSET=OEM" & Chr(0)
StrAttr = StrAttr & "COLNAMEHEADER=TRUE" & Chr(0)
StrAttr = StrAttr & "DEFAULTDIR=" & StrPath & "" & Chr(0)
StrAttr = StrAttr & "DESCRIPTION=dotwtext" & Chr(0)
StrAttr = StrAttr & "DSN=dotwtext" & Chr(0)
StrAttr = StrAttr & "EXTENSIONS=txt,asc,csv,tab" & Chr(0)
StrAttr = StrAttr & "FIL=text" & Chr(0)
StrAttr = StrAttr & "FILETYPE=Text" & Chr(0)
StrAttr = StrAttr & "FORMAT=csv" & Chr(0)
StrAttr = StrAttr & "MAXSCANROWS=25" & Chr(0)
StrAttr = StrAttr & "READONLY=FALSE" & Chr(0)
StrAttr = StrAttr & "DRIVERID=27" & Chr(0)


If Add = True Then
If SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, _
"Microsoft Text Driver (*.txt; *.csv)", StrAttr) Then

Else
MsgBox Err.Description, vbExclamation, "ETM Agent"
CreateDSN = False
End If
Else
If SQLConfigDataSource(0, ODBC_CONFIG_SYS_DSN, _
"Microsoft Text Driver (*.txt; *.csv)", StrAttr) Then

Else
MsgBox Err.Description, vbExclamation, "ETM Agent"
CreateDSN = False
End If
End If

Exit Function
errdsn:
For Each ErrObj In Errors
MsgBox ErrObj.Description, vbExclamation, "ETM Agent"
Next
CreateDSN = False
End Function


'------------Module Ends-----------------


Now From the Calling Form Use the Following Code


Dim ConText as new ADODB.Connection
ConText.CursorLocation = adUseClient


If Not IsDSNExist("dotwtext", eSystem) Then
If CreateDSN("dotwtext", sttmpPath, True) = False Then
ImportFromTXT = False
Exit Function
End If

Else
If CreateDSN("dotwtext", sttmpPath, False) = False Then
ImportFromTXT = False
Exit Function
End If
End If

If ConText.State = adStateOpen Then ConText.Close
ConText.Open "dotwtext"






And thats all to it .. I did it Finally ... And thanks to all of you who tried to give me solution for DSN less Connection ... I hope that No VB developers are gonna get freedom from these sort of Problems unless Microsoft stabilizes and Finalizes its Dlls ...