Howdy all. I have pretty much NO VB/Access coding experience, but am forced to come up with a generic script/process for users with varying database designs and varying levels of expertise to use and integrate into their Dbs.
Following is my script and is launched On_Open event. The script determines someone's desired upload class (test, archive or recent), executes a specific query based on that info, then writes the query results to a text file, tab-delimited. I am trying to use createQueryDef so that the query can be built on the fly with a temporary query - no need to make several permanent queries. As you might notice, the main problem is having to insert TOP 300 in the SQL query, the WHERE changes are quite manageable, the TOP 300 gets me.
Anyway, my diagnostic MsgBox right before the createQueryDef works, but I get stuck in some kinda error/hold after that, that requires ctrl+break to get out of.
Thanks for any insight!!! I am using Access 2000 right now, to ensure as much universal compatibility as possible, but that may be a mistake! -
Option Compare Database
-
-
Private Sub Report_Open(Cancel As Integer)
-
-
Dim uploadClass As Integer
-
Dim countDates As Integer
-
-
Set dbs = CurrentDb
-
Set rst = dbs.OpenRecordset("SELECT * FROM date_tracking")
-
-
If rst.RecordCount = 0 Then
-
response = MsgBox("Is this for a Test Upload?", vbYesNoCancel, "Upload Type")
-
If response = vbYes Then
-
uploadClass = 0
-
bp (uploadClass) 'run query and data export
-
ElseIf response = vbNo Then
-
response = MsgBox("Access will now generate an Archive upload.", vbOKCancel, "Upload Type")
-
If response = vbOK Then
-
uploadClass = 2
-
bp (uploadClass)
-
End If
-
End If
-
Else: response = MsgBox("Access will now generate an upload of new records since last.", vbOKCancel, "Upload Type")
-
If response = vbOK Then
-
uploadClass = 1
-
bp (uploadClass)
-
End If
-
End If
-
Set rst = Nothing
-
End Sub
-
-
Sub bp(uploadClass As Integer)
-
On Error GoTo bp_Err
-
-
Dim date_update As Integer
-
Dim fileName As String
-
Dim strTop As String
-
Dim strWhere As String
-
Dim strFields As String
-
Dim qryName As String
-
Dim strSQL As String
-
-
date_update = 1
-
-
'******** COPY AND PASTE FROM SQL VIEW OF Query *********
-
'----------------------------------------------------------------------------------------------------
-
-
strFields = " { copy & paste complex JOIN/ON/FROM query } "
-
-
'----------------------------------------------------------------------------------------------------
-
'*******************************************************************
-
-
If (uploadClass = 0) Then
-
strTop = "TOP 300 "
-
strWhere = " WHERE table1.date > Date()-1095 "
-
date_update = 2
-
ElseIf (uploadClass = 1) Then
-
strTop = ""
-
strWhere = " WHERE (((table1.date)<(SELECT Max([UploadDate]) AS maxDate FROM date_tracking;))) "
-
Else
-
strTop = ""
-
strWhere = " WHERE table1.date > Date()-1095 "
-
End If
-
-
strSQL = "SELECT " & strTop & strFields & strWhere
-
qryName = "bpQuery"
-
Set dbs = CurrentDb
-
response = MsgBox("I'm about to CreateQueryDef", vbOKOnly, "") ' for diagnostic purposes only
-
Set qdf = dbs.CreateQueryDef(qryName, strSQL)
-
response = MsgBox("I'm just went through CreateQueryDef", vbOKOnly, "") ' for diagnostic purposes only
-
Set qdf = Nothing
-
-
fileName = "C:/bp/" & DatePart("m", Date) & DatePart("d", Date) & DatePart("yyyy", Date) & ".txt"
-
DoCmd.OutputTo acOutputQuery, qryName, acFormatTXT, fileName, False, ""
-
' OR DoCmd.TransferText acExportDelim, "", qryName, fileName, False, ""
-
-
DoCmd.QueryDefs(qryName).delete
-
-
bp_Exit:
-
If date_update = 1 Then
-
Set rst = dbs.OpenRecordset("date_tracking")
-
rst.AddNew
-
rst![UploadDate] = Date - 1
-
rst.Update
-
rst.Close
-
End If
-
-
bp_Err:
-
date_update = 2
-
Resume bp_Exit
-
End Sub
-
Regards,
oh4real
2 2895
BTW, line 65-70 is where the createQueryDef is.
oh4real
Nevermind. Problems were at least 2-fold:
1) Tools -> References - were not all ticked, as per forum helpers! Don't remember which ones I ticked, so can't test if this was the original problem, but I think #2 is the real reason.
2) The QueryDefs.delete qryName line had 'db' for database, while it should been 'dbs'. Therefore the query was never deleted - this caused a primary failure of the CreateQueryDef (qryName, strSql) since it couldn't overwrite.
3) The _Err & _Exit had not Exit Sub in them, so got unending loops - once did .addnew 245,000 times ;)
Clear head, great night's sleep and refill on Adderall go a long way...
Sorry to bother,
oh4real
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Andrew |
last post by:
Hi:
I run into the following problem where I would use CreateQuerydef to create
a new Table and then use "Execute" to execute the statement. However, I
keep getting an error message saying that...
|
by: priscilla.jenkins |
last post by:
Alright, I'm really new to SQL and VBA and all this, so I might be
completely off course...but just tell me. I know C and Assembly, but
that doesn't help me much here.
I'm trying to create a...
|
by: Natt Serrasalmus |
last post by:
After years of operating without any coding standards whatsoever, the
company that I recently started working for has decided that it might be a
good idea to have some. I'm involved in this...
|
by: R Reyes |
last post by:
Hello All,
I'm always looking for ways to improve my code. Most of the time (whenever
I'm working on a project) I write a bunch of functions. Then after the
project is finished, I put all the...
|
by: Jon Maz |
last post by:
Hi,
I am writing inline code .aspx pages using VS.Net 2002. The color coding
seems to work for VB.NET inline, and not for C# inline (see below).
Does anyone know how to get this working for...
|
by: TJ Doherty |
last post by:
Need help understanding the following please:
When I am creating a project and code my connection using Dim connectString
As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data...
|
by: Electrified Research |
last post by:
I just want to know other peoples' views about this. Now that VS2005
is out, how do people feel about visual editors?
Personally I am so comfortable with hand coding that Visual Studio
along...
|
by: Expat |
last post by:
I have several queries that I create from SQL statements using Currentdb.CreateQueryDef in one of these cases the code breaks and refuses to create the query giving me a:
Run-time error 3131. Syntax...
|
by: rahuldev999 |
last post by:
Hi
I am beginner in the access project.
can anyone suggest how to deal with this
" Set rq = Db.CreateQueryDef("r_temp_rslt_research", strSql)"
in the below code.As its a MDB application...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |