Modules & VBA :: How To Execute SQL In Automation
Aug 7, 2015
I'm opening a second database (db2).
Copying the structure of a db2 table to db3.
Populating the table in db3 with a subset of records from db2.
I've gotten as far as opening the db2 and copying the table structure. Can't figure out how to run the query using execute rather than docmd.openquery.
Code:
Dim appAccess As Access.Application
Set appAccess = New Access.Application
appAccess .OpenCurrentDatabase "DbPathString"
'copy the table structure to dbQn, overwrites any previous with same name
[Code] ......
View Replies
ADVERTISEMENT
Aug 2, 2013
I'm trying to add a value from access into a combo box on a web page. I can get the combo box to refresh, but not add in a value..The HTML for the combo box is
<tr><td align="right">Campaign: </td><td align="left"><span id="LogiNCamPaigns"><select size="1" name="VD_campaign" id="VD_campaign" onfocus="login_allowable_campaigns()">
<option value=""></option>
And I've been using
ie.Document.all.Item("VD_campaign").focus
To refresh it (works great)
And attempting to put a value into it (which is an option in the combo box)
ie.Document.getElementById("VD_campaign").Value = "201 - Campaign-1"
But nothing happens - not even an error
View 1 Replies
View Related
Jul 31, 2015
I get the message "The expression On Click you entered as the event property setting produced the following error: Class does not support Automation or does not support expected interface"
I receive the message on a PC running Windows 7 Professional using the Access 2013 Runtime. This pc does not have Access 2013 installed.On my pc, I do not get the error. I have Access 2013 installed and run Windows 7 Professional SP1. The "code" which gives the error is as follows and is invoked by clicking a button on a form
Code:
MsgBox "1"
Dim rst As ADODB.Recordset
MsgBox "2"
Set rst = New ADODB.Recordset
MsgBox "3"
[code]....
The error takes place after Msgbox "2" and before Msgbox "3".The strange thing is that I can run without a problem a sophisticated software package on the pc which gives the error, using Access 2013 Runtime. This package I converted from Access 2003.
View 3 Replies
View Related
Feb 24, 2014
I'm trying to do something with Excel and Access. From Excel 2007, I need to open an Access database exclusive, import from Access to Excel a table, do some work within Excel, and then start a macro within the Access database. Briefly, here’s what I have
1) To open the database exclusively:
Set connDB = New ADODB.Connection
With connDB
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Mode = adModeShareExclusive
.Open strdbpath 'path to database
End With
2) To import from Access:
strSQL = "SELECT * FROM [" & tn & "];" 'tn is Access table
If connDB.State = adStateOpen Then
Set objRS = New ADODB.Recordset
objRS.Open strSQL, connDB, adOpenForwardOnly
On Error Resume Next
objRS.MoveFirst
If Err.Number = 0 Then
On Error GoTo 0
fieldCnt = objRS.Fields.Count
For fieldNum = 0 To fieldCnt - 1
ws.Cells(1, fieldNum + 1).Value = objRS.Fie(fieldNum).Name
Next fieldNum
ws.Cells(2, 1).CopyFromRecordset objRS
End If
End If
3) To start a macro:
DoCmd.RunMacro "Daily Import"
Everything seems to work until the ‘DoCmd’ statement where I get a “You can’t carry out this action at the present time” error message.
View 8 Replies
View Related
Aug 21, 2014
I am getting the following error message in strsql:You tried to execute a query thats does not include the specified expression 'ScanDate=20140730 and SCanhour=8' as part of an aggregate function in the following strsql.
Code:
Call func1("Z", 8)
Public Sub func1(b As String, a As Integer)
strsql = "SELECT Count(BatchNo) AS CountOfBatchNo, Sum(Envelopes) AS SumOfEnvelopes, Sum(Cases) AS SumOfCases, Sum(Pages) AS SumOfPages, ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') AS QueueNo " _
& "FROM jabberwocky " _
& "group By ScanDate, [Type] & Format([Type1],'00') & Format([Type2],'00') " _
& "HAVING ScanDate=" & J & " and Scanhour=" & a & ""
End SUb
View 8 Replies
View Related
Aug 1, 2013
I have just started to develop a database that will export data directly into a word template. I have used Word automation quite a lot but I'm new to trying to automate Word from Access.
It's going OK at the moment, I have got the db to open up the template, write data and then close. My objective is to add the data to multiple tables within word. So I have created several tables in my word template and then tried to select these tables and write to the them. Everything is thing for the first table but for any other table I get an error message saying that the member of the collection doesn't exit i.e. the table isn't there. I select the table using:
Code:
objWord.selection.tables (2).select
I then used:
Code:
objWord.selection.tables.count
To show how many tables were in the document and it doesn't matter how many there are, it always says there is 1 table.
why it can only see 1 table and what I can do to get around it?
View 2 Replies
View Related
May 14, 2015
I have programmed a letter using automation to Word VBA. The letter works like a mail merge so it might cycle thru several records when it runs. I've separated each letter in the document with a section break. I'm having a problem with the header. I've successfully added a header, but when it moves to the next record, it replaces the header in the entire document with the current record. I want each section to insert data from that record. How can I fix this? Below is a sample of my code (note: the linktoprevious doesn't seem to work either).
x = 1
'Create Header
With ActiveDocument.Sections(x)
.Headers(wdHeaderFooterPrimary).LinkToPrevious = False
.PageSetup.DifferentFirstPageHeaderFooter = True
[code]....
View 1 Replies
View Related
Sep 2, 2013
I'm trying to tick "Yes" to a radio button which are side by side on a web page (Yes and No). The HTML is below and the VBA I'm trying to use is:
ie.Document.all.Item("newsletter").Checked = "0"
I've also tried
ie.Document.all.Item("newsletter").Checked = "True"
ie.Document.all.Item("newsletter").value = "True"
ie.Document.all.Item("newsletter").value= "0"
<table class="form"> <tr> <td>Subscribe:</td>
<td> <input type="radio" name="newsletter" value="1" /> Yes
<input type="radio" name="newsletter" value="0" checked="checked" /> No
</td> </tr> </table> </div>
View 6 Replies
View Related
Feb 5, 2015
There is a form where whenever the form is closed, the below code needs to execute:
If IsNull(Me.CostPerPiece1.Value) = True And IsNull(Me.CostPerPiece2.Value) = True And IsNull(Me.CostPerPiece3.Value) = True And IsNull(Me.CostPerPiece4.Value) = True And IsNull(Me.CostPerPiece5.Value) = True Then
Me.AllowAdditions = False
DoCmd.SetWarnings (0)
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings (-1)
Else
Call AppendQuoteCharges
End If
The If Then just looks at if certain fields are all null and if so, it deletes the current record. If at least one field is not null, the AppendQuoteCharges routine is called.
The form has 4 or 5 navigation buttons that close this form and send you to a different one. I've added the above code to each of those buttons before it runs the DoCmd.Close Form. I also have a Exit button that runs a DoCmd.Quit. I developed this months back but I'm pretty sure I added the above code under each button's click event rather than a Form On Close event because Form On Close does not execute after a DoCmd.Quit command
In rare cases, the form is being closed without the routine being ran. I think if a user clicks the Close button in the top right of Access (the X), it might be running a DoCmd.Quit which is doesn't run this code.
How can I be sure that whenever the form is closed or exited, the code is ran? Is there a way to tie this code to the user clicking the X in the top right?
View 2 Replies
View Related
Sep 30, 2013
I'm using shellexecute in my form whenever a picture is clicked the respective program/ application will open to show the picture. Because I want to see the picture more clearly by zooming it in or out. But the problem is I don't know how to disable the delete button and prev./next button, because I want the user use the program only to zoom it in.
View 4 Replies
View Related
Jun 2, 2014
I am running Office 2010 but at one time also had Office 2013 installed on my Windows 7 64 bit OS PC. The first attachment (AccessProblem) shows the error message that I get when it hits the
Set olApp = CreateObject("Outlook.Application")
line. I get the automation error - library not registered error. The second attachment (Access2Problem) shows the references that I have.
View 2 Replies
View Related
Apr 10, 2014
I have a table which lists all documents that refer to a certain entity. the table contains the file names and paths. I would like the user to be able to search for text inside these documents.Can I use Windows Search for that by using code? Is there any other way?
View 6 Replies
View Related
Nov 19, 2013
I developed a finance tracker database for tracking project revenues and costs (forecast and actual amounts). I have a form where we enter a new Work Order in the DB. This Work Order form has a continous subform where we create all the billing periods needed for the life of the Work Order. Once the billing periods are created (opened) we can then add our revenue and cost forecasts for each period. We have a Billing Period lookup table that has our billing periods with their respective start and end dates (which usually begin around the last week of a month and end about 3 weeks into the next month).
For the purpose of this question, lets say we only enter the required WO_Number and WBS_Code (Composite key) and the Work Order Start_Date and End_Date. I want to click a button to runs some code to automatically create all the billing periods for which their start and end dates fall into the Work Order start and end dates.
For example, say a Work Order starts on 5/1/2013 and ends on 7/31/2013
The billing period dates in the Billing Periods lookup table are as follows:
May-13 ---> 4/22/2013 - 5/19/2013
Jun-13 ---> 5/20/2013 - 6/23/2013
Jul-13 ---> 6/24/2013 - 7/21/2013
Aug-13 ---> 7/22/2013 - 8/18/2013
Then we would need the following billing periods created in the Work Order subform:
May-13
Jun-13
Jul-13
Aug-13
I don't even know where to start on this. Is it possible to automate this process with the setup I have? If so, how would I structure the VBA code/logic to use the billing period lookup table and create entries in the subform for all the billing periods that fall into the duration of the Work Order.
The attached DB is a stripped down version with only the tables and forms needed for this problem.
View 2 Replies
View Related
Jun 18, 2013
I'm trying to create a function to update and amend records in a table.
The update part works and updates existing records with new data but I'm getting an error with the insert part.
Run time error 3078
The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.
Nothing called 'FALSE' so not sure what that means?
Code:
sSQL = "INSERT INTO Pupil_tb (PupilID,Class,PupilName,etc ) " _
= "SELECT PupilImport_tb.PupilID, Class, PupilName, etc FROM PupilImport_tb " _
& "LEFT JOIN Pupil_tb " _
& "ON Pupil_tb.PupilID=PupilImport_tb.PupilID " _
& "WHERE Pupil_tb.PupilID Is Null "
CurrentDb.Execute sSQL, dbFailOnError
View 5 Replies
View Related
Mar 3, 2007
I managed through a code on this forum to do compact and rapair on a button click as follows:
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction
but then the dialogue winodw pops up to ask me to opn the comacted databse or not.
I want to set that to open automatically after the compact and rapair.
reason is that I will run another code after that to backup the databse.
any way to that
View 13 Replies
View Related
Jan 23, 2006
Just had a query in my mind. I have a table master_data with 16 columns. so please tell me which one of the following would less time to execute.
1. select * from master_data;
2. select cust_name from master_data;
From the above example, i am interested to know, does number of parameters selected in the query effect the execution time.
View 2 Replies
View Related
Jul 3, 2007
I'v defined an update query as a string in my VBA code.
Then I execute the update query with : CurrentProject.Connection.Execute
however, I want the query out of the code and into the query folder where the other query's are.
How can I execute the query from within the VBA code when I do this ?
View 1 Replies
View Related
Dec 12, 2004
Can I execute a query I have stored in my Access database by using ASP from a webpage?
Thanks,
Been at the for 3 hours.
View 1 Replies
View Related
Jun 17, 2007
Hi all
I'm stuck on the following SQL statement. It looks OK to me... but there must be something wrong with it because it doesn't execute.
I've tried the runSQL command and I've tried currentproject. connection.execute as well. Both fail to execute... it has to be the SQL string below.
Thanks for any advice.
SQLImportPE = "UPDATE data INNER JOIN data_import ON data.ID = data_import.ID" & _
"SET data.pe1 = data_import.pe1, data.pe2 = data_import.pe2, data.pe3 = data_import.pe3, data.pe4 = data_import.pe4, data.pe5 = data_import.pe5, data.effort_pe = data_import.effort_pe" & _
"WHERE (((data_import.pe1) Is Not Null));"
View 10 Replies
View Related
Feb 22, 2006
I have a data bate that sends automated emails or it should. The thing is it will not open with the Icon there for it will not run with the windows scheduler. Has anyone ever seen this error before. I get this when I click on the Icon. But I can open in design and all works fine.
""Cannot find the file ;CDocumentts and Settingd41668
DecktopCalibrationDBAutoMailer.mdb(or one of its components). Make sure the path and file are correct and that all required libraries are available.""
View 1 Replies
View Related
Jun 19, 2006
I have created a Switchboard where users can enter data via a form and run reports. I tried testing by entering data into the form and switched over to see my entry in the report it's not there...
How do I automate it so that when the users run a report it updates it will their entries?
Access 2003
View 2 Replies
View Related
Jul 28, 2006
I would like to create a program within access that is linked to a specific folder on my C: drive and waits for a file to appear within the folder. Once the file appears, i would like access to process the file and return the output back to the folder. Is there a way to do this?
example: test.txt appears in folder C:Test. Access grabs the txt file, opens it, processes the information and then writes the output back to C:Test. I will need access to continously check the folder for new files.
thank you
View 1 Replies
View Related
Aug 17, 2007
Hey folks -
Is there a way to open a different data base from within another?
I would love to have a command button that closes the current database and opens a second. (My project is growing and it would make things much easier if the next several sections would be on their own - esspecially since they are unrelated to the main portion of the project).
Many thanks for your input,
t
View 2 Replies
View Related
Jun 16, 2007
Guys I am having a blond moment
I have a qry that i wish to export to xls - but i want it to be automated
what i have is transactions and I need to have these exported in xls on the following basis
Sterling transaction - with
tax type 1 (5%)
tax type 2 (2%)
tax type 3(N/A)
etc
then .
euro
tax type 1 (5%)
tax type 2 (2%)
tax type 3(N/A)
etc
Dollars
tax type 1 (5%)
tax type 2 (2%)
tax type 3(N/A)
etc
other currencies - South african Rand - Idian Ruples etc
all seperated out
by currency and by tax
now my reports - done - fine
the xls transfer is throwing me
I know there is a key word that shows me how to do it (So let me know this word and then i should be able to take this a step forward)
now how I intend on doing this (given my limited knowledge on coding) is to run 1 qry per currency per tax rate and export into a xls book and each qry to be on a seperate tab/sheet
so let assume that each currency will have 3 tax rates (there willb e more)
so i will need 3 per currency in the example above
sterling , euor and dollar giving 9 qry to make - now the qry themselves are easy no problem on this - but is this the best way to do this or is there a better way ??
your thought smuch appricated
g
View 14 Replies
View Related
Oct 11, 2004
Looking for a way to count the number of pages in a word document (while in MS Access)
Also looking to delete a page of Word while in Access.
View 1 Replies
View Related
Oct 26, 2004
In simple terms please explain what an Automation object is. The Access help is any help!
When I try and enter a value it comes up with "The object doesn't contain the Automation subject "Surname"" The only surname field I have is on the header of the form and is not related to the field that prompts the message.
Any ideas much appreciated
View 1 Replies
View Related
