in-code VB ODBC link and SQL Search

I am trying to connect to a Baan informix database for a VB application. At first, I was establishing a link via an access database, however Baan databases tend to be so huge that Access chokes when making the initial connection (some of the time). So to get around this I've been looking for a way to subvert the Access connection and make the link directly from VB. I've been searching Deja News and realized many people were trying to do the same. Had not come accross a post with a solution by the time I figured it out on my own, so I'm posting this here. To do this, I puchased the Intersolv DataDirect 3.10 ODBC driver (AKA CLI-32 driver). Make sure you version is 3.x, since the driver cannot function with over 16,000 tables otherwise. Note: the Informix db I'm working with is roughtly 60,000 tables. A system DSN using the ODBC Administrator is established with the name "DB". This is imperative, since the OpenDatabase method uses the DSN to locate the database. Public Function FindItem(Target As String, _ Source As String, _ Reference As String, _ Key As String) As String 'Makes Database connection to ODBC System DSN specified in ODBC 'Admin. Crossreferences a table in order to find corresponding information 'based on parameters passed. Returns value of SQL statement. NOTE - Both 'Key and SQL result must be unique in order for function to work as desired, if 'at all. 'Target -- Column containing desired information. 'Source -- Table upon which function is performed. It is assumed that the database in question is DBPath. 'Reference -- Column containing key search data with which to cross-reference. 'Key -- Search criteria. 'Declarations Dim ODBCWkSp As Workspace Dim DB As Database Dim RS As Recordset Dim SQL, Temp As String 'Initialize On Error GoTo Handle Set ODBCWkSp = CreateWorkspace("", "<User Account>", "<Acct PWD>", dbUseODBC) Set DB = ODBCWkSp.OpenDatabase("DB", dbDriverNoPrompt, False, "ODBC;DATABASE=baandb;UID=baan;PWD=baan;DSN=DB") SQL = "SELECT " + Target + " FROM " + Source + " WHERE " + Reference + " = '" + Key + "' ; " Set RS = DB.OpenRecordset(SQL) Temp = "Null" 'Body Temp = RS.Fields(Target) FindItem = Temp Handle: FindItem = Temp End Function -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading