INSERT INTO external DB wich has a DB PAssword
Windows 7 64bit 
Access 2007
I have a database called Scheduling.mdb. In it, I have a simple form called frm_CARD. Its record source is a table called ORDERS.
I also have another Database called Out.mde which has a database password - let's pretend the password is "1234". This database has a table called PATIENTS with fields that are identical to the fields in the ORDERS table (located in the Scheduling.mdb).
On the CARD form (located in the scheduling.mdb) I have a buttom called SEND. When this button is clicked I need to INSERT all of the values that have been entered into the current record on that form INTO the PATIENTS table (located in the Out.mde).
I simply cannot get any code to accomplish this task.
For reference, I do have a query that accomplishes the same thing for multiple records and with far more fields than I need in this task. Here is the SQL for that query:
INSERT INTO (C:\Out\Out.mde;PWD=1234) Patients ( [Grouped_Invoice#], Patient_First_Name, Patient_Last_Name, [Place #], [Cancelled?], Age, Height, Height_Inches, Weight, [Time], Exam, Diagnosis, [Room #], [Med_Rec_#], [For Dr], Prints_x, IV_Setup_x, Isotope_1, [Isotope_1_Lot#], Isotope_1_Dosage, Isotope_1_Inj_Time, Isotope_1_Inj_Site, Isotope_1_Time, Isotope_2, [Isotope_2_Lot#], Isotope_2_Dosage, Isotope_2_Inj_Time, Isotope_2_Inj_Site, Isotope_2_Time, [Tech Notes], Misc, Good, Acceptable, Unacceptable, Facility, [Sched Date], [Schedule #], [Taken By], Tech, ID, Item02, Item03, Item04, Item05, Item06, Item07, Item08, Item09, Item10 )
SELECT Orders.[Grouped_Invoice#], Orders.Patient_First_Name, Orders.Patient_Last_Name, Orders.[Place #], Orders.[Cancelled?], Orders.Age, Orders.Height, Orders.Height_Inches, Orders.Weight, Orders.Time, Orders.Exam, Orders.Diagnosis, Orders.[Room #], Orders.[Med_Rec_#], Orders.[For Dr], Orders.Prints_x, Orders.IV_Setup_x, Orders.Isotope_1, Orders.[Isotope_1_Lot#], Orders.Isotope_1_Dosage, Orders.Isotope_1_Inj_Time, Orders.Isotope_1_Inj_Site, Orders.Isotope_1_Time, Orders.Isotope_2, Orders.[Isotope_2_Lot#], Orders.Isotope_2_Dosage, Orders.Isotope_2_Inj_Time, Orders.Isotope_2_Inj_Site, Orders.Isotope_2_Time, Orders.[Tech Notes], Orders.Misc, Orders.Good, Orders.Acceptable, Orders.Unacceptable, Orders.Facility, Orders.[Sched Date], Orders.[Schedule #], Orders.[Taken By], Orders.Tech, Orders.ID, Orders.Item02, Orders.Item03, Orders.Item04, Orders.Item05, Orders.Item06, Orders.Item07, Orders.Item08, Orders.Item09, Orders.Item10
FROM Orders
WHERE (((Orders.Patient_Last_Name) Is Not Null) AND ((Orders.Facility) Is Not Null) AND ((Orders.[Sched Date]) Between Date() And (Date()+4)) AND ((Orders.[Schedule #])=1));
How can I convert this SQL statement to a VBA Procedure? Obviously I need to change the WHERE clause so that only the current record gets transferred. I can do tht by using the ID field - something like: WHERE Orders.ID = Me.ID
Reply With Quote
