>>>Using VFP 6.0 / Win 95.
>>>
>>>This is my first attempt at ODBC and I'm trying to programatically make a connection to an Access database after having gotten the database name and path from the user.
>>>
>>>Everything either
to go smoothly but then the SQLConnect command will not make a connection or else the Create Connection command will cause the Connection Designer to pop-up which I don't want the user to have to see.
>>>
>>>Here is one of my many attempts that looks like it should work but it doesn't.
>>>
>>> OPEN DATABASE c:\MyDirectory\MyDB.dbc EXCLUSIVE
>>>
>>> lcConnString = 'DSN=My Access 7.0;'
>>> lcConnString = lcConnString + 'DBQ=C:\MyDirectory\MyAccess.mdb;'
>>> lcConnString = lcConnString + 'DefaultDir=C:\MyDirectory;'
>>> lcConnString = lcConnString + 'Driver={Microsoft Access Driver (*.mdb)};'
>>> lcConnString = lcConnString + 'DriverId=25;' && Access
>>> lcConnString = lcConnString + 'FIL=MS Access;'
>>> lcConnString = lcConnString + 'UID=admin'
>>>
>>> CREATE CONNECTION AccessTst1 CONNSTRING lcConnString
>>>
>>> gnConnHand=SQLCONNECT('AccessTst1')
>>>
>>>
>>>Can anybody tell me what's missing here? The example above will still ask the user for the Data Sourse Name and database to connect to even though they are listed in the Connection String.
>>>
>>>Any Help would be appreciated.
>>>
>>>TIA
>>>
>>
>>First, do not use the PRE tags around your message. Only use them around code you want to leave as formatted.
>>
>>You only need to create the connection in the DBC once. Use the Connection designer to do this. But first, you need to create a DSN via the ODBC Administrator found in the Control Panel. Once you create the DSN, you will identify this as the data source on the Connection designer.
>>
>>You can browse the Win95 Registry to see what is entered for the DSN. Look in HKEY_CURRENT_USER\Software\ODBC\ODBC.INI
>>
>>To do this by code, I did the following for a VFP DSN:>>lcODBCDriver="Microsoft Visual FoxPro Driver"
>>lcSettingString = ; "SourceDB=\\MyServer\VOL2\APPS\PIMS99\DATA\Pims.dbc"+CHR(0)+;
>> "DSN=PCPIMS"+chr(0)+;
>> "Description=VFP Pims!Roster"+CHR(0)+;
>> "SourceType=DBC"+CHR(0)+;
>> "BackgroundFetch=Yes"+CHR(0)+;
>> "Exclusive=No"+CHR(0)+;
>> "Collate=Machine"+CHR(0)+;
>> "SetNoCountOn=No"+CHR(0)
>>
>>#define ODBC_ADD_DSN 1
>>#define ODBC_CONFIG_DSN 2
>>#define ODBC_REMOVE_DSN 3
>>#define ODBC_ADD_SYS_DSN 4
>>#define ODBC_CONFIG_SYS_DSN 5
>>#define ODBC_REMOVE_SYS_DSN 6
>>#define ODBC_REMOVE_DEFAULT_DSN 7
>>
>>DECLARE Integer SQLConfigDataSource in odbccp32 ;
>> Integer, Integer, String, String
>>
>>local lnSuccess
>>lnSuccess=SQLConfigDataSource(0,ODBC_ADD_DSN,lcODBCDriver,lcSettings)
>>return iif(lnSuccess > 0, .t., .f.)
>>The lcODBCDriver is the exact description of the driver to use. In your case, it will be the description of the Access drivers. You can find this in the Registry as well.
>
>
>Mark,
>
>Thanks for the reply but I'm still a little confused or at least am still missing something.
>
>Befor I start; using the PRE tags was the only way that I could send my original message. I kept getting the error message not to use double quotes, left brackets, IMG, etc., etc. none of which seemed to be in my message. The "rules of posting" mentioned the PRE tags around code samples so I tried this but still got the error message. I finally put the PRE tags around the entire message and it finally went through. If you could tell me what might have been causing the error in my original message above, it would keep me from repeating the same mistake.
>
>Now for the ODBC: Like I mentioned, I'm brand new to ODBC so I'm really in the dark even as to the nomenclature in most cases. What I'm trying to accomplish is to have a user select a database (from a GETFILE()?) and have my app make the connection without the user having to interact in anyway with the ODBC Administrator. From what I can understand, the code that you show above is to programatically create a DNS instead of having the user do it through the ODBC Administrator. My first goal is just to make the connection. As part of my attempts I had created a DNS manually and then tried to make a connection. I first did a CREATE CONNECTION and then an SQLConnect().
>
>Let's say that I created a DNS called MyDNS which pointed to a specific database; What do I do next? I did the CREATE CONNECTION which went fine but the SQLConnect with the connect string that referenced the DNS acted as if the DNS did not exist. That is what has me baffled.
>
>What am I doing wrong?
>
>TIA
>
>Ed
Not sure why you could not post message without pre tags. But I removed them in my original reply, and it was accepted. May have been a fluke.
I am now confused. If you have the user select a database (DBC), why do you need to create a connection to it? Can't you just issue an OPEN DATABASE (User_Selected_DBC) command and start opening tables or views from there?
To create a connection to a *remote* data source, you have to have a DBC open. A connection is a component of a DBC. To create a connection, you have to first create a DSN either via the ODBC Administrator or programmatically like my previous example.
The reason I created a DSN and connection to a VFP DBC was for an application that needed access to limited data in an existing app's DBC. This second app has its own DBC, so all I needed was a remote view to some of the data in the first app's DB. In the second app, I check the registry to see if the VFP ODBC drivers have been installed. If not, the user gets a message to contact me for support. It then checks to see if the DSN has been created. If not, it is created programmatically. The connection already exists in the second apps DBC. Because I check for everything being setup properly, I will not get any error since I terminate the app before any view is opened if any component is missing.
I designed it this way as a somewhat secure method of accessing personnel data on a Novell network. The second app has Novell Read-only access to the DBC files and certain DBC/CDX files from the first app. The user can not File-scan, edit, erase or create anything in the first apps DB directory. But since the data can be read, I can now create a remote view of the data in the second apps DBC.
I will be using an encryption library (Cryptor, NetLib, etc.) to secure the data from the first app to prevent hacking from the 2nd apps DB.
Mark McCasland
Midlothian, TX USA
