Using VBA to create a Table in Access database

[Cire via AccessMonster.com]

[quoted text clipped - 41 lines]

recordset could cause trouble. What version of Excel and Access are you
using?

i still got error pointing to " .Range("A2").CopyFromRecordset rst "
but when i cycle through the rest of the code using F8, i still end up with a
prompt to make changes to "Book1" even though i left the name as Results(i'm
using results instead of export). when i said yes and selected my desktop as
the save path, the export complete message appeared and the file was on my
desktop with all the data and the fields(after adding that extra code)

so apparently it manages to complete the recordset and copy from it but
somehow access vba still detects an error on that line. i'll post my whole
code up

Heres my code:

Private Sub cmd_RunQuery_Click()
On Error GoTo Error_Handler

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim sqlstring As String
Dim lngMax As Long
Dim lngCount As Long
Dim strPath As String

strPath = "C:\Results.xls"
strPath = InputBox("Enter Excel Path " & _
"(e.g. " & strPath & ")", _
"Results", strPath)
If Not strPath Like "*.xls" Then
Exit Sub
End If

sqlstring = "SELECT .....

sqlstring = "From .....

quot = "'"

' Incomplete criteria

If IsNull(Me.cbx_int_org.Value) Or IsNull(Me.txt_last_open_date.Value) Or
_
Not IsNull(Me.txt_yr_opened.Value) Then
MsgBox "Incomplete criteria, please fill in the mandatory criteria " & _
"before running the query. Click Ok to continue filling in the
criteria"

Set qdf = Nothing


' All tickets By GE,regardless of year and created before a user-input
date
Elseif Me.cbx_int_org.Value <> "Select All" And IsNull(txt_yr_opened.
Value) Then
sqlstring = sqlstring & " WHERE int_org.iorg_name = " & quot & Me.
cbx_int_org.Value & quot
sqlstring = sqlstring & " AND DATEADD(ss, request.open_date,'1970-1-1') <= "
& quot & txt_last_open_date.Value & quot
sqlstring = sqlstring & " ORDER BY request.open_date DESC, request.ref_num
DESC "

Set db = CurrentDb()
Set qdf = db.QueryDefs("all_requests")
qdf.Sql = sqlstring

DoCmd.Minimize

'DoCmd.OpenQuery "qry_append_all_requests"

Set rst = qdf.OpenRecordset(dbOpenForwardOnly)

If rst.EOF Then
MsgBox "No records to export", vbInformation
GoTo Exit_Handler
End If

If Len(Dir(strPath)) < 0 Then
Kill strPath
End If

Set xlApp = CreateObject("Excel.Application")

Set xlBook = xlApp.Workbooks.Add

Set xlSheet = xlBook.Worksheets.Add

With xlSheet
.Name = "Results"
.UsedRange.ClearContents
lngMax = rst.Fields.Count
For lngCount = lngMax To 1 Step -1
.Cells(1, lngCount).Value = rst.Fields(lngCount - 1).Name
Next lngCount
.Range("A2").CopyFromRecordset rst
End With

lngMax = xlBook.Worksheets.Count

For lngCount = lngMax To 1 Step -1
If xlBook.Worksheets(lngCount).Name <> "Results" Then
xlBook.Worksheets(lngCount).Delete
End If
Next lngCount

xlBook.SaveAs strPath

MsgBox "Export Complete", vbInformation

Set qdf = Nothing

rest of my code - more elseif statements
..
..
..
End If

i got a feeling my if else loops overlap each other somewhere...and i
understand how a function works now, if i make a function within the form and
declare it public, i can just invoke them into my sub cmd_click after each of
my elseif statements although i'm not sure whats the syntax of it. Thus for
testing purpose, i've put one set of code into one of the elseif statements
as shown above and enter criteria the same way as my elseif statement has
specified.

 

[Cire via AccessMonster.com]

[quoted text clipped - 12 lines]

somehow access vba still detects an error on that line. i'll post my whole
code up

Heres my code:

Private Sub cmd_RunQuery_Click()
On Error GoTo Error_Handler

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim sqlstring As String
Dim lngMax As Long
Dim lngCount As Long
Dim strPath As String

strPath = "C:\Results.xls"
strPath = InputBox("Enter Excel Path " & _
"(e.g. " & strPath & ")", _
"Results", strPath)
If Not strPath Like "*.xls" Then
Exit Sub
End If

sqlstring = "SELECT .....

sqlstring = "From .....

quot = "'"

' Incomplete criteria

If IsNull(Me.cbx_int_org.Value) Or IsNull(Me.txt_last_open_date.Value) Or
_
Not IsNull(Me.txt_yr_opened.Value) Then
MsgBox "Incomplete criteria, please fill in the mandatory criteria " & _
"before running the query. Click Ok to continue filling in the
criteria"

Set qdf = Nothing

' All tickets By GE,regardless of year and created before a user-input
date
Elseif Me.cbx_int_org.Value <> "Select All" And IsNull(txt_yr_opened.
Value) Then
sqlstring = sqlstring & " WHERE int_org.iorg_name = " & quot & Me.
cbx_int_org.Value & quot
sqlstring = sqlstring & " AND DATEADD(ss, request.open_date,'1970-1-1') <= "
& quot & txt_last_open_date.Value & quot
sqlstring = sqlstring & " ORDER BY request.open_date DESC, request.ref_num
DESC "

Set db = CurrentDb()
Set qdf = db.QueryDefs("all_requests")
qdf.Sql = sqlstring

DoCmd.Minimize

'DoCmd.OpenQuery "qry_append_all_requests"

Set rst = qdf.OpenRecordset(dbOpenForwardOnly)

If rst.EOF Then
MsgBox "No records to export", vbInformation
GoTo Exit_Handler
End If

If Len(Dir(strPath)) < 0 Then
Kill strPath
End If

Set xlApp = CreateObject("Excel.Application")

Set xlBook = xlApp.Workbooks.Add

Set xlSheet = xlBook.Worksheets.Add

With xlSheet
.Name = "Results"
.UsedRange.ClearContents
lngMax = rst.Fields.Count
For lngCount = lngMax To 1 Step -1
.Cells(1, lngCount).Value = rst.Fields(lngCount - 1).Name
Next lngCount
.Range("A2").CopyFromRecordset rst
End With

lngMax = xlBook.Worksheets.Count

For lngCount = lngMax To 1 Step -1
If xlBook.Worksheets(lngCount).Name <> "Results" Then
xlBook.Worksheets(lngCount).Delete
End If
Next lngCount

xlBook.SaveAs strPath

MsgBox "Export Complete", vbInformation

Set qdf = Nothing

rest of my code - more elseif statements
.
.
.
End If

i got a feeling my if else loops overlap each other somewhere...and i
understand how a function works now, if i make a function within the form and
declare it public, i can just invoke them into my sub cmd_click after each of
my elseif statements although i'm not sure whats the syntax of it. Thus for
testing purpose, i've put one set of code into one of the elseif statements
as shown above and enter criteria the same way as my elseif statement has
specified.

oh and i just forgot one thing. about the exported file, some of my data who
is in date time format appears as integers in excel. previously when i used
the docmd.openquery method and then export that to excel, the date was fine.
i had to select the whole column and go to format cells and select date
before it converted back to date. any workaround for this?

 

[Cire via AccessMonster.com]

[quoted text clipped - 116 lines]

as shown above and enter criteria the same way as my elseif statement has
specified.

oh and i just forgot one thing. about the exported file, some of my data who
is in date time format appears as integers in excel. previously when i used
the docmd.openquery method and then export that to excel, the date was fine.
i had to select the whole column and go to format cells and select date
before it converted back to date. any workaround for this?

hmm i tried the make-table query method and its working. but the access file
size will increase..is there anyway to make access AUTO compact the database
upon exit? i.e. on my exit button, access will delete all the tables formed
and compact the database.

 

[Brian Wilson]

oh and i just forgot one thing. about the exported file, some of my data

who
is in date time format appears as integers in excel. previously when i
used
the docmd.openquery method and then export that to excel, the date was
fine.
i had to select the whole column and go to format cells and select date
before it converted back to date. any workaround for this?

I'm not sure which part of the world you are in, but I guess we may be
separated by a timezone. I'm in England, UK and it's just turned 7am.
I'll deal with the formatting first as this should be simple. After you
have done the CopyFromRecordset, you could loop through the columns and set
the NumberFormat to whatever you like. In the example below, I look at the
Type property of the DAO.Field, but I could just pick a column and set the
NumberFormat property regardless of the type:

With xlSheet

.Name = "Export"

.UsedRange.ClearContents

lngMax = rst.Fields.Count

.Range("A2").CopyFromRecordset rst

For lngCount = lngMax To 1 Step -1

.Cells(1, lngCount).Value = rst.Fields(lngCount - 1).Name

Select Case rst.Fields(lngCount - 1).Type

Case dbDate
.Columns(lngCount).NumberFormat = "dd-mmm-yy"

Case dbLong
.Columns(lngCount).NumberFormat = "000000"

Case Else
' Do not change the formatting.

End Select

Next lngCount

End With




As to the error, you are experiencing, I really am not sure. One thing we
spoke about earlier is subs and functions, and really this one single sub is
too big and there's too much code in a single place. When you come to debug
(or ask someone else) there just seem to be too many lines and it makes you
just want to go back to bed! For example, where you work out the SQL and
amend the query you could put this bit in a function:

Private Function UpdateQuery(strQueryName As String) As Boolean
' Blah, blah, blah
' Return True if the query was updated
End Function


Then in your sub, you can write <If Not UpdateQuery("MyPassthrough") Then
Exit Sub> Then we know that bit is working, and there is no need to look at
the code which updates the query. You could split other bits up as well,
and the first check I would do would be to ignore the bit with Excel and
make sure you can open the recordset without error, and loop through all the
records
While Not rst.EOF
rst.MoveNext
Wend
MsgBox "Looping Finished"


You previously said you had an error here
Set rst = qdf.OpenRecordset(dbOpenForwardOnly)

Has that error gone? You could try changing that to
Set rst = qdf.OpenRecordset()


Failing that there is always Google. I just had a look for possible errors
and one which turned up was if your recordset contained OLE objet fields.
See http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246335 for
further details.

 

[Brian Wilson]

Hey Brian, i've got a prob, access detected an error in this line:
"Set rst = qdf.OpenRecordset(dbOpenForwardOnly)"

[quoted text clipped - 116 lines]

as shown above and enter criteria the same way as my elseif statement has
specified.

oh and i just forgot one thing. about the exported file, some of my data
who
is in date time format appears as integers in excel. previously when i
used
the docmd.openquery method and then export that to excel, the date was
fine.
i had to select the whole column and go to format cells and select date
before it converted back to date. any workaround for this?

hmm i tried the make-table query method and its working. but the access
file
size will increase..is there anyway to make access AUTO compact the
database
upon exit? i.e. on my exit button, access will delete all the tables
formed
and compact the database.

If you went this route, I would programatically create a temporary db with
the export table in the same folder as the front end then create a linked
table in the front end. I can think of variations, but I would not create
this table in the same db as the main data.
But the CopyFromRecordset method froma pass-through query should be a
better, faster option if you can get it working.