Modules & VBA :: How To Execute A DoCmd From Within A Connection State

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 Replies


ADVERTISEMENT

Modules & VBA :: Change Label To Display Full Name Of State On Form

Jan 1, 2014

I tried this code to change the label to display the full name of the state on the form. Is there a shorter way than putting 49 more states after the if?

[CODE]Private Sub cmdStates_Click()
DoCmd.OpenForm "frmChurchesAll"
DoCmd.ApplyFilter "qryFindState"
lblTxtSt = txtState
If txtState = "FL" Then
lblTxtSt = "Florida"
End If
lblTxtSt.BackColor = 15658720
lblTxtSt.Visible = True
lblStatesof.Visible = True
lbAllChurches.Visible = False
End Sub /CODE]
D7

View 8 Replies View Related

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 4 Replies View Related

Modules & VBA :: Mail Merge - Database Placed In A State By User That Prevents It From Being Opened Or Locked

Jun 19, 2014

I have the following code which errors on line

Code : .OpenDataSource Name:=CurrentProject.FullName, SQLStatement:="SELECT * FROM [qryMailMerge]"

The error (Error has occurred: The database has been placed in a state by user 'Admin' on machine 'W74XXXXXX' that prevents it from being opened or locked) appears in the Word document.

Code:
Public Sub CreateWordToPDF(strWordFile As String, strPDFFile As String, strSQL As String)
'Call CreateWordToPDF("C:Doc1.doc", "C:Doc1.pdf", "SELECT * FROM [qryMailMerge]")

Dim objWord As Word.Application
Dim docWord As Word.Document
'Open MS Word using early binding.
Set objWord = New Word.Application
Set docWord = objWord.Documents.Open(strWordFile)

[Code]......

View 8 Replies View Related

Modules & VBA :: Multiple Conditions In A DoCmd

Oct 11, 2013

I have a Customer Issue form that writes the following into one table named Table1: Date, Customer Name, Ticket number, Agent, Issue and Comments. This is very simple.

All of this is filled in from a form that has links to 3 other tables for drop downs; Customer Name, Agents, Issues. This is working perfectly.

What I want to be able to do is generate a report based on a date range for a particular agent. Say 9/1/2013 - 9/30/2013 for John Doe.

For the report portion, on the form I have to combo boxes, one for start date, one for end date. I also have a drop down for selecting the agent. When I click a button on the form named Report, It will generate a preview of the report.

Here is the problem. I can get this to generate a report based either on the date range, which gives me all of the agents, or by agent, which gives me all of the dates. I can't get it to do both.

Here is some code that I have on the Report button:

DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent
This is the code that will let me choose the agent, but gives me all dates.

If I change this code to this:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
It will display all issues in the date range, but gives me all agents.

I was thinking I should be able to combine them with an AND or an & to get it to use both the agent and date fields, but I can't get this to work.

Something like: DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent And "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

or

DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent & "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"

View 3 Replies View Related

Modules & VBA :: DoCmd Works In Immediate Window Not In Sub

Nov 14, 2014

Why does the DoCmd.TransferDatabase work in the Immediate window, but not in the Sub, which has always worked in the past?

Code:
Public tblname As String
Public tblNewname As Variant
Public pstrDatabasePath As String
Public Const dbType As String = "Microsoft Access"

[code]....

ERROR MESSAGE: 3125 '' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

View 4 Replies View Related

Modules & VBA :: Execute A Query That Does Not Include Specified Expression

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

Modules & VBA :: Command To Execute When Form Is Closed Or Quit

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

Modules & VBA :: How To Disable Buttons From Another App Which Is Called By Shell Execute

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

Modules & VBA :: How To Execute Free Text Search In Linked Documents

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

Modules & VBA :: Update And Amend Records In A Table - Error Trying To Execute SQL Statement

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

How To Establish The Connection Between Ms-Access2000 & Oracle Using DAO Connection?

Sep 26, 2005

Dear Guys,
How to Establish the connection between Ms-Access2000 & Oracle using DAO Connection through Code?.

I created one table in Oracle and I created User-DSN in ODBC.
I linked that oracle table into Ms-Access MDB using Link table option in ms-Access.

Now, Whenever I open that mdb, we need to establish the ODBC connectivity automatically through code (I mean, we need to refresh automatically).

I need to hardcode all the information in the Ms-Access code.

My User DSN Name: abcd
Oracle Database Name: abcd
Schema Name(User Name): abcd
password: abcd

View 1 Replies View Related

Modules & VBA :: Check If There Is Connection To Server

Oct 8, 2013

In the code below I want to wait for ten seconds if there is connection to the server, if not to exit the sub with message "No connection to server". How to check it

Dim req As New XMLHTTP60
Dim resp As New DOMDocument60
Dim xmlNodeList As IXMLDOMNodeList
Dim myNode As IXMLDOMNode
Dim xmlNode As IXMLDOMNode
Dim presription As IXMLDOMNode

req.Open "GET", "https://api.zdravstvo.gov.mk/rest/prescriptions/" & Me.br_recept, False
req.send
resp.LoadXML req.responseText

View 2 Replies View Related

Modules & VBA :: Remote Connection String?

Aug 30, 2013

I am trying to put together a vba adodb connection string to a remote server. It is the first time I am using adodb in this context. I can get msysobj.connect but how do I format that information into a connection object connection string?

View 3 Replies View Related

Modules & VBA :: How To Check If Connection To SQL Server Established

Apr 8, 2014

How I can check if connection to SQL server is established with VBA before the SQL is executed. btw I use VPN connection to connect to sql server, and some times VPN connection is going down in middle of work and it is quite unconvinced.

View 2 Replies View Related

Modules & VBA :: Excel Data Connection Not Updating?

Mar 17, 2015

I have an excel spreadsheet that uses a data connection to a website to download text. I have the data connection properties to refresh upon opening the file. If i double click the file from windows, it updates. Although using the code below doesn't work. When i've added the code ObjXL.Save before closing it, it usually saves a file with no data and no cells. I'm eventually going to switch visible to false.

Private Sub btnDownLoadSettle_Click()
Dim XLapp As New Excel.Application
Dim ObjXL As Excel.Workbook
Set ObjXL = XLapp.Workbooks.Open("C:...Settlements.xlsx")
ObjXL.Application.Visible = True
ObjXL.Windows(1).Visible = True
Sleep (5000)
ObjXL.Close
XLapp.Quit
End Sub

View 2 Replies View Related

Modules & VBA :: Connection Cannot Be Used - Error On Production But Not Development

Jul 24, 2013

old Access database that's been upgraded through to Access 2000 or 2003 (probably 2000), but not beyond. Was rebuilt in Access 2003 format around 2006/2007 (by someone else who no longer works here). Being used now in Access 2007 and 2010. Uses DSN to connect to SQL Server backend. Last week it was SQL Server 2005, moved the back end on Saturday to SQL Server 2012. Changed the DSN when moving to the new server. But I don't think this has to do with the DSN (it's getting data just fine).

There's a data entry form. User enters a Generator ID, event triggers it to look for the details for the generator and load them into a generator subform. After it loads the information into the subform, and before the user does anything else, it throws "This connection cannot be used to perform this operation. It is either closed or invalid in this context."It triggers post update of the Generator ID:

Code:

Private Sub txtGeneratorID_AfterUpdate()
On Error GoTo Handle_err
Me.txtGeneratorID = UCase(Me.txtGeneratorID)
Call FillHandlerSubform(Me.subGeneratorInfo, Me.txtGeneratorID.Value)
If GetGenStat(Me.txtGeneratorID.Value) = "N" Or GetGenStat(Me.txtGeneratorID.Value) = "OB" Then
MsgBox "Warning Generator Status! " & UCase(Me.txtGeneratorID.Value) & vbCrLf & _
"This Generator has a status of N or OB!", vbCritical, "Bad Generator Status!"
Cancel = True
End If

[code]....

It doesn't throw this error in the development copy of the database, dev copy works just fine. It only throws it from the production version. They are located on the same network, just in different folders. They are pointing to the same database on the same SQL Server using a DSN file located inside the folder where the .mdb file is located (this is a change in the front end, prior to this it was using a DSN on each individual machine, but I changed it to stop that).

View 6 Replies View Related

Modules & VBA :: DB Bloat And Persistent Connection To Linked DB

Mar 16, 2015

I read a lot about Access database bloat when using temporary tables that reside in the front end, which was where I created and used temporary tables before reading about bloat.I therefore wrote a routine on startup that creates a temporary database and then copies the structure of the temporary tables into that temporary database using the DoCmd.TransferDatabase command. It kills the DB on exiting the application.I also read about keeping a persistent connection (handle) open to a linked DB as long as the main application is active, and this for efficiency reasons. I got this to work as well.

Being a self-taught Access programmer I have two questions:

1.Is the use of docmd.Transferdatabase a good way to work and does it reduce bloat by transferring the structure of the temp files to the temporary database?
2.If I want to work with the tables in my temporary database I use the following code for example:
Dim dbTemp As Database temporary database
Set dbTemp = DBEngine.Workspaces(0).OpenDatabase(strDBTemp)
Set rstRpt = dbTemp.OpenRecordset("tblRptPU001", dbOpenDynaset) ..
My question is this: if, at the end of the procedure, I write
dbTemp.close
set dbTemp = Nothing

does this leave the persistent connection open? The reason for the question is that I intend to split the database and soon go multi user, which is when efficiency is even more important than when working on a temporary database.

View 4 Replies View Related

Modules & VBA :: Verify A Network Connection / Bad File Name Or Number

Mar 22, 2014

I have a picture stored on a network file share that populates each time a particular form is opened. Every so often we lose our connection (which is a different issue altogether). Right now the error I get after much grinding away, is "Bad file name or number"

Is there a quicker way to check for the connection before it spends 45 seconds trying to find the whole path?

Code:
Dim vFolderPath As String, dirFile As String, strFile As String
vFolderPath = Nz(DLookup("FolderName", "tblCodes-FolderControl", "FolderKey = '" & "Profile" & "'"))
dirFile = vFolderPath & Dir(vFolderPath & ctrl_people_id & " *", vbDirectory)
strFile = dirFile & "profile_pic.*"
'Debug.Print dirFile
On Error Resume Next
If Dir(strFile) <> vbNullString Then
Me.[ctrl_ImageFrame].Picture = dirFile & "" & Dir(strFile)
Else
Me!ctrl_ImageFrame.Picture = "X:~stuffprofile_icon.png"
End If

View 4 Replies View Related

Modules & VBA :: Automatically Linking Database With ODBC Connection

Jun 26, 2015

I am looking to automate the process of linking my Access DB with an ODBC connection to an SQL DB with VBA (unless there's an easier way to do it?) - some sample code - if this is possible at all?

View 2 Replies View Related

Modules & VBA :: Refresh Linked Tables When Connection Lost Without Closing Access Database

Sep 4, 2014

I have MDB database linked to SQL SERVER through VPN connection.I created links to the sql server Links are dsnless..Everything works fine but when I lost VPN connection or sql connection has been broken I can't refresh links to the tables.I receive message 3146 sql connection failed..I must close database and start again...

I tried different methods like ado,dao, and vba docmd.transferdatabase,aclink... but no success, table cant be relinked.

Only way I can relink is to change ip adress in conn.string

E.g. 192.124.0.2 (1st ip- router server ip) and after connection failed i can use 192.124.0.32 (2nd ip - server local ip) and that's it if i lost connection for the 3rd time... i must restart application.

It seems that access database keep the previous connection..how to reset or drop database connection to the sql server and refresh links to the tables with vba code without closing access database...

View 12 Replies View Related

DNS Connection --&gt; DNSless Connection

Jan 9, 2005

Right Ok i hope i can get some help cause im absolutely stumped..
i cant get my login page to work since moving it from my PWS(iis on xp) DSN connection
to a 24/7 server on the net with DSNless connection
here is the code for the include file "conntopwd.asp"
http://www.pritchardwebdesign.pwp.blueyonder.co.uk/conntopwd.txt
the username and path has been changed slightly for security purposes,
and the code for the login page which attempts to access the database is here
http://www.pritchardwebdesign.pwp.blueyonder.co.uk/login.txt
the connection has been tested by one of the tech support guys at brinkster and he told me it was ok,
the problem was the way that the data is called from the data base is wrong ..
The tech support guy said that it was this line that was causing the problem..
MM_rsUser.ActiveConnection = MM_conntopwd_STRING
Any help would be much appreciated as im stuck for now..

View 2 Replies View Related

Unexpected State

Jul 13, 2005

Ok, I know you guys have heard about this problem quiet a few times, my search told me that. But my problem has a bit of a twist. The message appeared when I tried to open the database yesterday, so I went to the backups and tried to open them. All the backups for the past month give the same message. The problem is with the FE only, the BE is working fine. The backups are connected to the same workgroup as the FE and BE. Has anyone seen this before and know what might of happened?

View 8 Replies View Related

Determine State

Mar 28, 2006

I am trying to determine the state that a job is located in. If the ProjectID begins with a 2 then it is in California. If it does not then the job is located in Nevada.

State: IIf(Left([tblMainFrontierUnits].[ProjectID],1)="2","CA","NV")

What is wrong with this statement? I am getting a compile error.

View 5 Replies View Related

City State Zip??

Aug 26, 2005

Does any one know of a place online where you can get the tables (for free I hope) for a city state zip comboboxes, or at least the tables for all cities in the US.

View 8 Replies View Related

Check Box State

Dec 9, 2005

If the check box is null (grey) - the default value, I do not want to include it is the sql statement. If I use the code below I get the error 94 Invalid use of null. How else can I exclude this ststement is the check box is grey.

thanks

If CheckStaged Then
strWHERE = strWHERE & " AND s.Staged = " & CheckStaged
End If

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved