C# Insert with two MS Access databases

[ewokspy]

I have 2 MS Access 2003 databses, one is third party so I cannot modify it, and the other is a db I designed to grab the info I need and add a few other columns. I am writing a C# program to perform an Insert of all the records that exist in the 3rd party db, but not in my DB, but I cannot figure out how to do this.

I originally wrote the code in Access VBA via a form timer, a linked table to the third party db (CZPrintJobs) into my db, and the below SQL statement. this worked, but I was told that C# 2003 would be the better way to handle this. I'd rather not link the table as it's twice the space and mean location is less flexible.

I realize the oleDBcommand only contains 1 db connection, so I think I have to run this as a select command, and then an insert, but even the select command would require accessing both dbs. Help would be most appreciated!

"INSERT INTO PAPrintJobs ( Id, Copies, Printer, Computer, Owner, Document, SubmittedDate, SubmittedTime, JobStatus, TotalPages, Cost )" +
				"SELECT CZPrintJobs.Id, " +
				"CZPrintJobs.Copies, " +
				"CZPrintJobs.Printer, " +
				"CZPrintJobs.Computer, " +
				"CZPrintJobs.Owner, " +
				"CZPrintJobs.Document, " +
				"CZPrintJobs.SumittedDate, " +
				"CZPrintJobs.SumittedTime, " +
				"CZPrintJobs.JobStatus, " +
				"CZPrintJobs.TotalPages, " +
				"CZPrintJobs.Cost " +
				"FROM CZPrintJobs " +
				"LEFT JOIN PAPrintJobs " +
				"ON (CZPrintJobs.Owner = PAPrintJobs.Owner) AND " +
				"(CZPrintJobs.Id = PAPrintJobs.Id) AND " +
				"(CZPrintJobs.SumittedDate = PAPrintJobs.SubmittedDate) AND " +
				"(CZPrintJobs.SumittedTime = PAPrintJobs.SubmittedTime) " +
				"WHERE (((CZPrintJobs.Id) Like 'O*') AND ((PAPrintJobs.Id) Is Null) AND ((CZPrintJobs.JobStatus) = 'Printed'));";

[vanc]

You can try using two connections to those two databases, because it is not two connections to one database, so it should work fine. I don't see any obstacles here?!! or I may not understand your problem.

cheers.

[ewokspy]

Well here is the code I am using to try and perform the append statement:

		public void StartDatabaseLoop()
		{
			
			DateTime lastDay = DateTime.Today;
			OleDbConnection dbCZConnection = 
					new OleDbConnection(configData[0] + this.CZPath);
			OleDbConnection dbPAConnection = 
					new OleDbConnection(configData[0] + this.PAPath);

			this.loopBool = true;

			while(loopBool)
			{
				
				//Open a connection to the DB
				dbCZConnection.Open();	
				dbPAConnection.Open();	
			
				OleDbDataAdapter myAdapter = new OleDbDataAdapter();	

				//Create the SQL Command
				OleDbCommand myAppend = new OleDbCommand(
						BuildSQLAppendString(), dbPAConnection);
				
				int iRows = myAppend.ExecuteNonQuery();

				dbCZConnection.Close();
				dbPAConnection.Close();
				
				//Wait append time or for interruption
				ThreadController.WaitOne(1000*this.AppendInterval,false);

			}//end while


		}

Note at Line 22 I am calling the dbCommand, but it will only allow me to supply one connection, not two. Even with the tables in the same database and using the supplied query the query doesn't seem to go through. In essence I am asking how you do a select statement that accesses two difference tables in two different Access databases, since once I can get that select statement I can then append the results in a seperate insert command.

[vanc]

Well here is the code I am using to try and perform the append statement:

		public void StartDatabaseLoop()
		{
			
			DateTime lastDay = DateTime.Today;
			OleDbConnection dbCZConnection = 
					new OleDbConnection(configData[0] + this.CZPath);
			OleDbConnection dbPAConnection = 
					new OleDbConnection(configData[0] + this.PAPath);

			this.loopBool = true;

			while(loopBool)
			{
				
				//Open a connection to the DB
				dbCZConnection.Open();	
				dbPAConnection.Open();	
			
				OleDbDataAdapter myAdapter = new OleDbDataAdapter();	

				//Create the SQL Command
				OleDbCommand myAppend = new OleDbCommand(
						BuildSQLAppendString(), dbPAConnection);
				
				int iRows = myAppend.ExecuteNonQuery();

				dbCZConnection.Close();
				dbPAConnection.Close();
				
				//Wait append time or for interruption
				ThreadController.WaitOne(1000*this.AppendInterval,false);

			}//end while


		}

Note at Line 22 I am calling the dbCommand, but it will only allow me to supply one connection, not two. Even with the tables in the same database and using the supplied query the query doesn't seem to go through. In essence I am asking how you do a select statement that accesses two difference tables in two different Access databases, since once I can get that select statement I can then append the results in a seperate insert command.

Sorry about this late, I'm too busy with the current project, can't remember anything. In this case and I understand your idea. I never hear about get data in two database with only one query statement, it sounds great but I don't think we can do that.
To do this we make another way, make to select queries, then manually merge the results to get the goal you want. If you understand your query, of course you do, and what it does behind the scene, then you can easily manipulate your data.
Two connections, two commands, two data readers, ...... one result :).

cheers.

[ewokspy]

Thank you. That pretty much confirms it with other sources I've asked. I do understand that the response is to merge the databases into one, but sometimes that just isn't possible...I wonder when the functionality to do this type of query will get implemented...

OH well. thanks.

[vanc]

Thank you. That pretty much confirms it with other sources I've asked. I do understand that the response is to merge the databases into one, but sometimes that just isn't possible...I wonder when the functionality to do this type of query will get implemented...

OH well. thanks.

Yes, who knows!!!

cheers.