Paradox to ODBC SQL Connection Login Screen

Does anyone know of a way to get rid of the pop up login screen when connecting BDE to ODBC SQL server? I have found places that state you can store the login information, but that does not seem to work. Is there a specific ODBC driver I should be using that will work better than the SQL Server Native Client 11 (this seems to work the best for data types and such)?

Make sure there is only one entry in HKEY_LOCAL_MACHINE\SOFTWARE\Borland\Database Engine\CONFIGFILE01.  Sometimes if you've tried to install more than once, the entry gets duplicated with a semi-colon inserted between the values.  Delete the second entry including the semi-colon.

Brad,

I have PFW programs that write thousands of SQL Server transactions every day using ODBC SQL Server.  I open the database when first starting the program and then pass the variables back and forth as required.  The following four code items are stored at the form level for my main application form.

In summary:

- I defined a custom type in the Type() event

- I defined some form level variables

- I created the method openProgramFiles() to collect parameters and open the database (This also opens my custom SQLLIB library that contains the rest of my re-usable SQL related code)

- I updated the form open() event to call openProgramFiles()

- Remember to close the database on the form's close() event.

At the form level:

Type
   varArray = dynarray[] anytype
   strarray = array[] string
endType

Var
 sqldbname, appDBname string
 rdb database
 dbparmsDA dynarray[] anytype
 customLib, sqlLib library
 outputArray vararray
endVar

method open(var eventInfo Event)

 if eventInfo.isPreFilter() then
  ;// This code executes for each object on the form:

 else
  ;// This code executes only for the form:

  if not openProgramfiles()
   then disableDefault
        errorclear()
        close()
  endif

 endif

endmethod

method openProgramFiles() logical
var
 tc tcursor
 currentSes session
 newpass string
endvar

currentSes.open()     

newpass = "XXXXX"
currentses.addPassword(newPass)

if not tc.open(":CUSTOMAPP:LOGIN.DB")
 then msgInfo("Error Starting Program",
   "Unable to open table with login data.  Call Technical Support")
   return false
endif
tc.movetorecord(1)
 
outputArray["DB User Name"] = tc."user_name"
outputArray["DB Password"] = tc."user_password"
outputArray["DB Alias Name"] = "SQLDATA"
outputArray["Application Alias Name"] = "CUSTOMAPP"
CustomLib.PassArray(outputArray)
tc.close()

appDBName = outputArray["Application Alias Name"]
sqlDBName = outputArray["DB Alias Name"]
dbParmsDA["USER MODE"] = "READ/WRITE"
dbParmsDA["USER NAME"] = outputArray["DB User Name"]
dbParmsDA["PASSWORD"] = outputArray["DB Password"]
 
;Test Opening the SQL database
try rdb.open(sqldbname, dbparmsDA)
 onfail
  msgInfo("Error Opening SQL  database",
 "Unable to open SQL database for setting up program.\nAborting procedure")
 errorshow()
  return false
endtry
rdb.close()

;Open the SQL library
if not sqllib.open(":L:SQLLIB")
 then
  msgInfo("Error Opening Library",
 "Unable to open SQLLIB library.\nAborting procedure")
 errorshow()
 rdb.close()
  return false
endif

return true

endmethod

Bradley, I read thru your response and it appears that you're posting the the SQL db only thru the library.  I have a situation that I need to post to the SQL db from many different forms and passing all of the data thru to a library would be extensive.  Is there any other way to open the SQL db for the entire paradox session?

I'm concerned about performance if we have to open the db connection on each and every form.

Thanks for your help!

Beth

Brad, 

That worked perfectly! Thank you so much for your assistance, we really appreciate it!