My thought is to create the temp table then export into Database 1 - which will replace the existing with the new data - or possibly have the Database2-tbl linked in Database1 and do a delete and append queries - however, then I run into the conversion issue. Note the date field = SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, PURCHASE_DATE that needs the conversion within the code or using the alter tables Thanks, Karen Here is my existing code.
Public Function FTCSConnection() Dim s Conn As String Dim o Conn As ADODB.
Now the Oracle data source contains numerous fields that need to be converted in 1 way or another, including converting a date that in contains a text (varchar) datatype ie. Connection Dim ctl As Control Dim j, I As Long Dim rs Field, tbl Field As String Dim rs Value, tbl Value As String Dim var NM As Variant ' Nomenclature_Modifier Dim var LSD As Variant ' Last Service Date Dim var RNG As Variant ' Range Dim var SDD As Variant ' Service Due Date Cmt Dim var EQL As Variant ' Equipment Location Dim var PAmt As Variant ' PURCHASE_AMT Dim var PDate As Variant ' PURCHASE_DATE On Error Go To FTCSConnection_Error Do Cmd.
You cannot add, delete, or modify the fields in a linked table while working in Access.If the SQL Server database contains a large amount of data, you should choose linking over importing, because the maximum size of an Access database is 2 gigabytes (minus the space needed for system objects).Aside note, this database structure is the result of my app originally being a MS Access App which I ported to VB6. I could have moved off MS Access in VB6 or C# but it is a very easy to use desktop DB solution.In the central database, I created a table called linkedtables with three columns Table Name, Linked Table Name and Database Name.I've coded this kind of thing in Access many times, and my approach for each table is: For numeric fields you'd have to use your available SQL dialect's function for converting Null to zero.
Running Jet SQL, I'd use Nz(), of course, but that doesn't work via ODBC. In any event, the point is to issue a bunch of column-by-column SQL updates instead of trying to do it row by row, which will be much less efficient.Suggested fix: --I too have this same problem with Access, ODBC drivers, and linked tables.I have tried various versions (that I could find) of the ODBC drivers with the same result.Here is my solution to relinking DAO tables using C#.My application uses a central MS Access database and 8 actual databases that are linked in.Linking lets you connect to data without importing that information, so that you can view and edit the latest data both in the SQL Server database and in your Access database without creating and maintaining a copy of the data in Access.