Hi, got a slight problem. As part of an ASP website I'm trying to insert into two linked tables in an access database using SQL. Standard SQL syntax woiuld be:
INSERT ALL INTO tblParent(name, Address) Values ('Dave','Home')
INTO tblChild(age, phone) Values ('35','1234567');
Thing is, does Access SQL support the INSERT ALL INTO statement? I keep getting syntax errors and I can only guess it might be this. if so, how do you make multiple table inserts into Access through SQL?
I have 5 different tables that are all linked with foreign keys. When I made my fifth table and linked it to my primary key like I had with the other tables I got the Join expression not supported error.
[Microsoft][ODBC Microsoft Access Driver] Selected collating sequence not supported by the operating system
To give you an idea of whats goin on... I took an excel file used xls converter and made it into a access database... I uploaded the database to the server and this is the error I got in dreamweaver... So I tried to used the MySQL Migration Tool Kit to make this a MySQL database and THis is the error I got when I was trying to execute the bulk transfer... Any Ideas? Please?
I am trying to execute the query below with multiple left joins because of the data I am trying to get back. The weird thing is sometimes it work and then sometimes it gives me a join expression error. It seems that access strangely removes brackets around the ON clauses. However even when I put those brackets back in this query it isn't working. why this query isn't running or why the brackets disappear in Access 2010.
SELECT Patients.[First Name] & " " & Patients.[Surname] AS Fullname , [Clinic Patient].[MYMOP ID] , NZ(MYMOPs.[MYMOP Date], [Clinic Patient].[First Appointment]) AS [MYMOP1 Date] , MYMOPs.Completed AS [MYMOP1 Completed]
the databases functionally normally with no issue. I have several databases one for each project and they are all having this issue. It doubles the size of the DB after the failure. This seems to tell me that the crash is just after the DB is ready to rewrite the database compacted. I run C&R about every 2 weeks and have a spread sheet tracking DB size prior to and after C&R. The compactions run from about 1% to 15% the norm seams to be 4% to 6%. The size of the DBs run from 65KB to 100KB.
I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have Table 1 with one field called Field 1 - values A, B, C Table 2 as follows
Field 1.....Field 2.......Field 3 A.............100 C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3 FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table2.Field3="XXX";
I have an application that uses Oracle 8.i remotely. The application selects tables from Oracle, opens them, and ultimately uses the selected open table to create a temporary Access table on a PC's hard drive; the user can then manipulate the Access data locally and then save the data back to the remote Oracle database.
This works great on several PCs where I work, however one PC is giving me fits. When I attempt to select an Oracle table to create the local Access table, I get the following message: "Run-time error '-2147467259 (80004005)': Data type is not supported"
We are using Jet Providers internally and are using Jet 4.0. The PC causing fits is using Access 2003.
Might the problem be caused by incompatibility between Access 2003 and Jet 4.0? Has anyone else encountered this issue? I tried searching this forum and also the Microsoft web site, but did not have luck. This is driving me slightly more nuts.
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID) SELECT Person_ID, Department_ID FROM TBL_NEWDATA WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
I have a normalized DB with one to many relationships, using Primary and Foreign Keys.
I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.
How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.
I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details
I have 2 tables, tblName and tblPhone. 1 name can have many phones.
tblName has PKName. tblPhone has PKPhone, FKName.
I have a form where user enters a new Name and PhoneNumber.
Name gets inserted to tblName, assigned with PKName = 100
Phone should get inserted into tblPhone with PKName.
IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").
The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.
I have a question.....i am uing Access as the back-end database of a VB6 Front end app. Basically i have a recordset with about 300K records....i currently Insert 1 row at a time....which takes ALOT of time.....is there a faster way to do the insert? i saw some things on the internet about a BULK insert....but it sounds like it is only with SQL Server or ORACLE. Please let me know what you think.
I have a 2 identical databases. One named customerrent and the other customerhistory. Both databases contain related tables. tblcustomer tblrent What i would like is a button on my form to archive the current record, to the customerhistory database then delete that same record from the customerrent database. Can someone give me an example how to accomplish this?
What would be the best way to do the following scenario:
I have a form where users can select continents. Another combo box is autofilled to show the respective countries. In that same combo box there is also an "All" option. So for North America the combo box would be filled with U.S., Mexico, Canada, and All. There are many other text/combo boxes that the user fills in and then finally a table is update with the record.
I want to be able to insert into a table identical records for U.S., Mexico and Canada if the user selects "All" in the countries combo box.
I have a database that is tracking proposals and job orders. First the proposal goes out and then sometimes the customer what to change the order in some way which means i need to put a new row somewhere other then the end of the record. I know in Access you can not add a row in the middle of a table but everyone was use to doing this in Excel and was able to make such adjustments. I thought about adding a Sort column so the data entry person could add their own sort order but some proposals can be hundreds of lines and no one wants to do this, however they want to be able to make changes and adjustments to the existing proposal. Is there some magic I could create in the development of this database to make everyone happy?????
Let me explain my situation first. I have a form that has a complete wells information(I'm doing Gas Balancing)
Every month we get new gas balancing statements. When a new year starts, I need to copy most of the info from one record to another- but make it a new year.
I have a form that displays all of the information. I am trying to make it so they push a command button for a new year, input what year, and then it copies the required information over- then refreshes the current filter(so that after they add a new year, they can go to the new year they just created)
I have googled for about 3 hours, and found a ton of differnt formats for Insert Into statements. Here is my latest(that does not work)
Dim strYear As Integer
strYear = InputBox("What year would you like to add to this well?")
DoCmd.RunSQL "INSERT INTO Gas_Balances (Sec, Twn, Rng, [Interest Owner], [Well Name], Year, County, State, WI%, NRI%, [Follow up Date], Comments, Operator) VALUES ( Sec, Twn, Rng, [Interest Owner], [Well Name], strYear, County, State, WI%, NRI%, [Follow up Date], Comments, [Operator_Operator])"
The values I want input to the table are from the current form(which is brought from a query of the Gas_Balances table).
I'm sure this is an easy fix, and I hate to bother the forum with what is probably a stupid question, but I have just about given up(it is 2:30 in the morning, I have to be up in 3 hours, and have spent the last 5 hours just trying to get something to work).
If anyone has any ideas/an example, please let me know. Thanks a lot everyone.
On the creation of a reconrd in the main table I'm trying to auto fill a sub table with standard information for the user. This is what I'm using to insert into the sub table:
I need to do something in excel but I have never used excel before. I need to make a form with a box. When you click on the box it shhould promt you to add a picture to the box. It should also format the picture so that it is the same size as the box. So far we have the box, but we still need to figure out what code to input so that it prompts you to add an image and then resizes it to fit the box. This is the code we have so far:
With Application.FileDialog(msoFileDialogFilePicker) .Title = "Seleccione la imagen" .Filters.Add "All Files", "*.*" .Filters.Add "JPEGs", "*.jpg" .Filters.Add "Bitmaps", "*.bmp" .FilterIndex = 3 .AllowMultiSelect = False .InitialFileName = Application.Path result = .Show If (result <> 0) Then fileName = Trim(.SelectedItems.Item(1)) Image1.Picture = LoadPicture(fileName)
End If End With
I am not sure if this can be done in excel or if I have to use another application to do this. I need to figure this out within the next couple of days, so if you have ANY idea of how to do this please post a response.
Have a database that stores information about jewelry. The user wants each record to have a picture of the item. No matter what I try I can’t put a picture field in my table. All help is appreciated. tillessal@yahoo.com :confused:
I have a db which has a main switchboard for 3 seperate forms/data sheets. I want to enter a column in one datasheet, but am not able to. I have checked properties, allow edits, deletions, etc, and has no relationships other than what the wizard would place between switchboard and form. Any help would be appreciated.
