INSERT ALL INTO - Supported Or Not?
Apr 26, 2006
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?
Cheers, Lol
View Replies
ADVERTISEMENT
Mar 8, 2007
Anyone can help me with this problem
In the following code
Dim qMain As ADODB.Recordset
Set qMain = New ADODB.Recordset
qMain.ActiveConnection = CurrentProject.Connection
qMain.CursorType = adOpenStatic
Dim strSql As String
strSql = "select MainTable!LotNo, MainTable!RollNo, FilmTest!RollNo, FilmTest!Av_56, FilmTest!Av_112,FilmTest!TestDate,FilmTest!TestTim e" + _
" from MainTable LEFT JOIN FilmTest ON (MainTable!RollNo = FilmTest!RollNo) "
qMain.Open (strSql)
When excuted, the error was Join expression not supported
View 5 Replies
View Related
Dec 18, 2013
I am using vb.Net to connect to an access db. In access 2010 this query works fine, but in vb.Net, it errors with 'JOIN expression not supported'.
Code:
sql = "SELECT Boxes.Box, Boxes.CustRef, Boxes.Customer " &
"FROM (Requests INNER JOIN [Request Boxes] ON Requests.[Request no] = [Request Boxes].[Request no]) INNER JOIN Boxes ON [Request Boxes].Box = Boxes.Box" &
"WHERE (Requests.[Request no]) = '" & item & "' " &
"AND ((Boxes.Customer) = '" & customer2 & "'))"
View 3 Replies
View Related
Feb 4, 2015
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.
View 14 Replies
View Related
Sep 8, 2006
Any one know what this means?
[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?
View 1 Replies
View Related
Jul 1, 2015
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]
[code]....
View 14 Replies
View Related
Sep 4, 2014
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.
View 1 Replies
View Related
Nov 10, 2006
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";
but I get Join Expression not supported
What am I doing wrong?
Thanks
Andrew
View 7 Replies
View Related
Jan 19, 2006
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.
Thanks in advance to all who reply!
View 2 Replies
View Related
Aug 28, 2014
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.
View 1 Replies
View Related
Jul 2, 2010
Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.
TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }
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.
View 2 Replies
View Related
Mar 25, 2015
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.
View 2 Replies
View Related
Dec 19, 2006
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.
View 2 Replies
View Related
Jun 10, 2005
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?
Thanks in advance!
Tim
View 1 Replies
View Related
Feb 27, 2008
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.
View 14 Replies
View Related
Mar 26, 2006
Hi,
I need urgent help. I have a question to ask and wonder if anyone might be able to help me.
My question is how do i execute SQL insert into a table called Job Register in access.
Here is my code before update
If foundflag = False Then
'new job detected
StrSQL = "INSERT INTO [Job Register](Industry_No, Client_No, Job_No, Job_Name, Job_Contact, Job_Phone, Job_MPhone, Job_Fee, Job_Reference, Job_Manager, Date_Registered) "
StrSQL = "VALUES ('" & Me.Combo20 & "', '" & Me.Text2 & "', '" & Me.Text4 & "', '" & Me.Text8 & "', '" & Me.Text10 & "', '" & Me.Text12 & "', '" & Me.Job_Mphone & "', '" & Me.Text14 & "', '" & Me.Text16 & "', '" & Me.Job_Manager & "', '" & Me.Date_Registered & "')"
End If
Your help is greatly appreciated.
Thank you very much in advance
View 3 Replies
View Related
Sep 18, 2006
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?????
Any suggestions greatly appreciated.
View 2 Replies
View Related
Oct 22, 2004
HI,
I have the statement as follows. But it is giving me the error message 3134(insert problem).
Assigning ERate = 7.1
DoCmd.RunSQL "INSERT INTO Currency (Exchange_Rate)VALUES(" & ERate & ")"
Currency is the table with 3 fields.
Curr_ID(Auto Generated,Primary Key)
Curr_Name(Text)
Exchange_Rate(Double)
Any ideas?
Thanks in advance.
View 5 Replies
View Related
Jan 10, 2005
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.
-Justin
View 4 Replies
View Related
May 9, 2005
hey guys i'm really furious abt tis. i don know wat is wrong with tis sql insert statement but whenever i execute it says tat syntax error in insert into statement:
>
strSqlCmd = "INSERT INTO VENDOR " & _ "(Name,Address,Club,Phone,Email,UserName,Password)" & _
"VALUES ('" & strCName & "','" & strAddress & "','" & _
strClub & "','" & strPhone & "','" & strEmail & "','" & strUName & "','" & _
strPwd & "')"
>
> tis is my statement
View 1 Replies
View Related
Feb 1, 2006
when i use an INSERT Sql function to update my database i get a popup warning telling me " You are about to append 1 row(s)".
Is there a way of bypassing this warning so the database is just updated no fuss ?
thanks in advance
john
View 4 Replies
View Related
May 11, 2006
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:
db.Execute "INSERT INTO tblHistologySlides (HistID, HistSlide, HistEntBy) VALUES (" & Me.HistAutoNo & ", " & Me.Text58 & ", " & Me.HistEntBy & ")"
It's stepping through the code without problem but the data isn't going into the table.
Any ideas where I'm going wrong?
Thanks
D
View 3 Replies
View Related
Jun 27, 2007
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.
View 2 Replies
View Related
Nov 27, 2007
Hello,
My problem is the pop up message that appears when I do the insert and update into my tables. It is annoying and i'd like to take it off...
Is there any way to take that off??
Thanks in advance.
View 4 Replies
View Related
Feb 27, 2005
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:
View 1 Replies
View Related
Jul 15, 2005
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.
View 1 Replies
View Related
