ADOX, ODBC & TQuery question (Long)

From: Stacey R. Brodsky (sbrodsky68_at_^nospam^aol.com)
Date: 01/16/04

  • Next message: Ignacio Vazquez: "Re: ADOX, ODBC & TQuery question (Long)" 
    Date: Fri, 16 Jan 2004 12:02:04 -0500

    Hi All,

    I have just started playing around with creating .mdb files at run-time, and
    then connecting using ODBC.

    I am using D5, Win2K...I have a TDatabase, TQuery & TDatasource in my data
    module. I connect to the .mdb file using the following code :

    const
      ODBCDRIVERKEY = '\Software\ODBC\ODBC.INI';
      DATASOURCES = ODBCDRIVERKEY + '\ODBC Data Sources';
      CRROAMING_DSNNAME = 'CRRoamingData'; {Database Alias}
      CRROAMING_FILENAME = 'CRROAMNG.MDB'; {Database Filename}

    procedure TfrmFrontDesk.CreateODBCUserDSN(DSNName, FileName : string);
    var
      Reg : TRegistry;
      JetKey : string;

    begin
      Reg := TRegistry.Create;
      try
        Reg.RootKey := HKEY_CURRENT_USER;

        Reg.OpenKey(DATASOURCES,True);
        Reg.WriteString(DSNName{DSNNAME},'Microsoft Access Driver (*.mdb)');

        Reg.OpenKey(ODBCDRIVERKEY + '\' + DSNName{DSNNAME},True);

        Reg.WriteString('DBQ', IBNetworkDataPath+FileName{GetMDBName});
        Reg.WriteString('Driver',GetWindowsSystemFolder + '\odbcjt32.dll');
        Reg.WriteString('FIL','MS Access;');
        Reg.WriteString('UID','Admin');
        Reg.WriteString('PWD','');
        Reg.WriteInteger('SafeTransactions',0);
        reg.WriteInteger('DriverId',25);

        JetKey := ODBCDRIVERKEY + '\' + DSNName + '\Engines\Jet';
        Reg.OpenKey(JetKey,True);

        Reg.WriteString('ImplicitCommitSync','');
        Reg.WriteInteger('MaxBufferSize',2048);
        Reg.WriteInteger('PageTimeout',5);
        Reg.WriteInteger('Threads',3);
        Reg.WriteString('UserCommitSync','Yes');

      finally
        Reg.Free;
      end;
    end;

    Now, if I run 'select * from roamingdata' via my TQuery, I get all of the
    records back, and very quickly. But, if I try 'select * from roamingdata
    where member_number = 27', it brings back no rows at all. And yes...there is
    definitely a member_number 27.

    Here is the creation of the .mdb file :

    procedure TfrmMain.Button1Click(Sender: TObject);
    var
      Catalog : _Catalog;
      Table : _Table;
      BaseName : String;
      DS : String;
      ADOCommand : TADOCommand;
      x : Integer;
      FirstName, LastName : string;
      FNameList, LNameList : TStringList;
      FNameCnt, LNameCnt : Integer;

    begin
      if Trim(edtDatabaseName.Text) = '' then begin
        MessageDlg('A database name must be supplied...please try again.',
    mtWarning, [mbOK], 0);
        Exit;
      end;

      // Name of the new database file
      BaseName := edtDatabaseName.Text;
      // Create dir if not exists
      ForceDirectories(ExtractFilePath(BaseName));
      // Create a Catalog Object
       Catalog := CreateCOMObject(StringToGUID('ADOX.Catalog')) as _Catalog;
      // Set the Connection String
      DS := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+BaseName;
      // Check if we already have such a file and delete it
      if FileExists(BaseName)
        then DeleteFile(BaseName);

      // Create new Access database
      StatusPane.Caption := 'Creating...';
      Application.ProcessMessages;
      Catalog.Create(DS);

     try
      ADOCommand := TADOCommand.Create(nil);
      FNameList := TStringList.Create;
      LNameList := TStringList.Create;

      with ADOCommand do begin
        ConnectionString := DS;

        try
          ADOCommand.CommandText :=
            'CREATE TABLE ROAMINGDATA ' +
            ' ( ' +
            ' UID CHAR(20) PRIMARY KEY, ' +
            ' ACCOUNT_NUMBER INTEGER NOT NULL, ' +
            ' MEMBER_NUMBER INTEGER NOT NULL, ' +
            ' FIRST_LAST CHAR(100), ' +
            ' FIRST_INIT_LAST_NAME CHAR(50), ' +
            ' HOME_PHONE CHAR(12), ' +
            ' HOME_PHONE_STRIPPED CHAR(7), ' +
            ' WORK_PHONE CHAR(12), ' +
            ' WORK_PHONE_STRIPPED CHAR(7), ' +
            ' MEMBERSHIP_TYPE CHAR(50), ' +
            ' ADDRESS_1 CHAR(100), ' +
            ' HOME_CLUB CHAR(3), ' +
            ' ALLOW_ENTRY BIT, ' + // Boolean
            ' ACCESS_MESSAGE MEMO, ' +
            ' ACCESS_COLOR INTEGER, ' +
            ' ACCESS_FONT_COLOR INTEGER, ' +
            ' BARCODE CHAR(12) ' +
            ' )';
          ADOCommand.Execute;

          // Create indices
          ADOCommand.CommandText := 'CREATE INDEX IDX_ACCOUNT_NUMBER ON
    ROAMINGDATA (ACCOUNT_NUMBER)';
          ADOCommand.Execute;
          ADOCommand.CommandText := 'CREATE INDEX IDX_MEMBER_NUMBER ON
    ROAMINGDATA (MEMBER_NUMBER)';
          ADOCommand.Execute;
          ADOCommand.CommandText := 'CREATE INDEX IDX_FIRST_INIT_LAST_NAME ON
    ROAMINGDATA (FIRST_INIT_LAST_NAME)';
          ADOCommand.Execute;
          ADOCommand.CommandText := 'CREATE INDEX IDX_HOME_PHONE_STRIPPED ON
    ROAMINGDATA (HOME_PHONE_STRIPPED)';
          ADOCommand.Execute;
          ADOCommand.CommandText := 'CREATE INDEX IDX_WORK_PHONE_STRIPPED ON
    ROAMINGDATA (WORK_PHONE_STRIPPED)';
          ADOCommand.Execute;
          ADOCommand.CommandText := 'CREATE INDEX IDX_BARCODE ON ROAMINGDATA
    (BARCODE)';
          ADOCommand.Execute;

          StatusPane.Caption := 'Created!';
          Application.ProcessMessages;

          // Add records here
          Randomize;

          FNameList.LoadFromFile('c:\word lists\names\sb-first-names.txt');
          LNameList.LoadFromFile('c:\word lists\names\sb-last-names.txt');

          FNameCnt := FNameList.Count;
          LNameCnt := LNameList.Count;

          ProgressBar.TotalParts := REC_COUNT;
          ProgressBar.PartsComplete := 0;
          StatusPane.Caption := 'Inserting...';
          Application.ProcessMessages;

          for x := 1 to REC_COUNT do begin
            ProgressBar.IncPartsByOne;

            if ProgressBar.PartsComplete mod 10 = 0 then
              Application.ProcessMessages;

            FirstName := FNameList[Random(FNameCnt)];
            LastName := LNameList[Random(LNameCnt)];

            FirstName := StringReplace(FirstName, '''', '''''', [rfReplaceAll]);
            LastName := StripToAlphaNumeric(LastName);

            ADOCommand.CommandText :=
            'INSERT INTO ROAMINGDATA '+
            '( '+
            'UID, '+
            'ACCOUNT_NUMBER, '+
            'MEMBER_NUMBER, '+
            'MEMBERSHIP_TYPE, '+
            'FIRST_LAST, '+
            'FIRST_INIT_LAST_NAME, '+
            'HOME_PHONE, '+
            'HOME_PHONE_STRIPPED, '+
            'WORK_PHONE, '+
            'WORK_PHONE_STRIPPED, '+

            'ADDRESS_1, '+
            'HOME_CLUB, '+
            'ALLOW_ENTRY, '+
            'ACCESS_MESSAGE, '+
            'ACCESS_COLOR, '+
            'ACCESS_FONT_COLOR, '+
            'BARCODE '+

            ') '+
            'VALUES '+
            '( '+
            '''TSTBBRMNG'+IntToStr(x)+''', '+
            IntToStr(x)+', '+ //ACCOUNT_NUMBER
            IntToStr(x)+', '+ //MEMBER_NUMBER
            '''GOLD MEMBERSHIP'', '+ //MEMBERSHIP_TYPE
            ''''+FirstName+' '+LastName+''', '+ //FIRST_LAST
            ''''+Uppercase(FirstName[1]+LastName)+''', '+ //FIRST_INIT_LAST_NAME
            '''561-741-0961'', '+ //HOME_PHONE
            '''7410961'', '+ //HOME_PHONE_STRIPPED
            '''561-746-3392'', '+ //WORK_PHONE
            '''7463392'', '+ //WORK_PHONE_STRIPPED

            '''123 Main Street'', '+ //ADDRESS_1
            '''TST'', '+ //HOME_CLUB
            '0, '+ //ALLOW_ENTRY
            '''Go ahead and let em in!'', '+ //ACCESS_MESSAGE
            {IntToStr(clGreen)+}'1, '+ // ACCESS_COLOR
            {IntToStr(clBlack)+}'0, '+// ACCESS_FONT_COLOR
            '''TST1234567'''+
            ')';

            {ShowMessage(ADOCommand.CommandText);}
            ADOCommand.Execute;
          end;

          StatusPane.Caption := 'Completed!';
          Application.ProcessMessages;

          ShowMessage('Database and table successfully created!');
        except
          on E:Exception do begin
            ShowMessage('Error in create table/indices : '+e.message);
            Exit;
          end;
        end;
      end;
     finally
      FreeAndNil(LNameList);
      FreeAndNil(FNameList);
      FreeAndNil(ADOCommand);
     end;
    end;

    Can anyone tell me what I am doing wrong here??? If I go into access and
    create a query selecting member_number = 27, it brings back the correct row.
    Any suggestions are welcome.

    Thanks In Advance,
    Stacey R. Brodsky

  • Next message: Ignacio Vazquez: "Re: ADOX, ODBC & TQuery question (Long)"
    • Quantcast