Trying to run an SQL command in the VBA to insert the current date and time into a table but I keep getting a syntax error? Any help?
Thanks
DoCmd.RunSQL "INSERT INTO tblAccessLog " _
& "User, DateLoggedIn, TimeLoggedIn) VALUES " _
& "(Username,Date,Time);"
The Username value is a variable declared within the code, and the Date and Time values are just the built in VBA functions.
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.
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.
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?
Thanks in advance!
Tim
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.
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
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.
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.
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
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
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
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
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.
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.
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 RelatedI 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.
I have a table SL with some fields, the first of them are: CODNOU and CODADR. I have a query test with some fields, the first of them are COD_NOU and COD_ADR. I made the following query:
INSERT INTO SL ( CODNOU, CODADR )
SELECT COD_NOU, COD_ADR
FROM test;
which intends to insert the data from test qry into to the table SL. Unfortunatelly when I run the qry a message occures and invites me to input the parameter value for COD_NOU. If I avoid using the field COD_NOU in the wry, the qry works correctly. What`s wrong?
Thank you in advance!
Hello,
From a subform I'm opening a popup form to a new record. Except the new record must insert the CustFK value. Right now its just filtering the record set. How can I insert the CustID value in the DoCmd.GoToRecord , , acNewRec form?? see VBA code below.
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ComDetail"
stLinkCriteria = "[CustFK]=" & Me![CustID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Hi, with SQL code, I was wondering if/how it is possible to use more than one SELECT statement in an INSERT INTO statement.
So, basically, I want my code to look something like this but I am not sure of the correct syntax:
INSERT INTO tablename
VALUES
(SELECT........), ('value 1)
(SELECT........) ('value 2)
Hi, with SQL code, I was wondering if/how it is possible to use more than one SELECT statement in an INSERT INTO statement.
So, basically, I want my code to look something like this but I am not sure of the correct syntax:
INSERT INTO tablename
VALUES
(SELECT........), -> value 1
(SELECT........) -> value 2
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
hi,
is it possible to have an insert query to fill some of the parameters by select query from other table and rest of the parameters by passing the value.
ex:
insert into table1(a,b,c) values (10,select b fom table2 where.... , 35);
i tried with the above syntax but was unable to succeed.
if this is not possible can anyone help me in solving the problem
thanks,
aravind
Hello,
I have one question regarding the use of an INSERT INTO query. I tried the following code, which I found on http://www.techonthenet.com/sql/insert.php, however unfortunately it does not work.
The text on the website says: “The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.” However, it just produces an error code, which says that Jet cannot find the dual table. Why doesn’t it work?
INSERT INTO tbX ( Col1, Col2, Col3 )
SELECT 'Test', 15, #3/3/2007#
FROM dual
WHERE not exists (select * from tbX where tbX.Col1 = 'Test');
Additionally, I try to do the same thing via Excel. I use here I code, which I found in a forum, too. However, unfortunately it doesn’t work as well… any hints?
Option Explicit
Private adoCn As ADODB.Connection
Private adoRs As ADODB.Recordset
Private adoCmd As ADODB.Command
Sub OpenDatabase()
Dim sDBPfad As String
sDBPfad = "C:TestQuestion.mdb"
Set adoCn = New ADODB.Connection
With adoCn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & sDBPfad
.Open
End With
End Sub
Sub InsertData()
Call OpenDatabase
Set adoRs = New ADODB.Recordset
With adoRs
.ActiveConnection = adoCn
.Source = "tbX"
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
.AddNew
.Fields("Col1").Value = "TEST"
.Fields("Col2").Value = 99
.Fields("Col3").Value = #3/3/2007#
.Close
End With
End Sub
Thank you.
Diana
Can someone tell me how to insert a blank row after every 4th row of data?
Here is the query:
SELECT B.NAME, B.WORKER, C.[SUB SYS] INTO D
FROM B, C;
data comes back like this
NAMEWORKERSUB SYS
GORDON N PAUL0000ACRT
GORDON N PAUL0000AENF
GORDON N PAUL0000AFIN
GORDON N PAUL0000ARAP
GORDON N PAUL000ATCRT
GORDON N PAUL000ATENF
GORDON N PAUL000ATFIN
GORDON N PAUL000ATRAP
I want it like this
NAMEWORKERSUB SYS
GORDON N PAUL0000ACRT
GORDON N PAUL0000AENF
GORDON N PAUL0000AFIN
GORDON N PAUL0000ARAP
GORDON N PAUL000ATCRT
GORDON N PAUL000ATENF
GORDON N PAUL000ATFIN
GORDON N PAUL000ATRAP