Help with using VBA to create a table from a pass-through query

[Cire]

Hi all, i have a problem, not sure if its good or bad. Let me 1st tell you what i've done.

i've used querydefs(mdb) to create a query, which is pass-through to the SQL server backend and subsequently used docmd.openquery to open the query. all these code is in a cmdrunquery_click command button.
The problem is that when my users export these data to excel, excel chops off data. i.e. if the data is more than 256 chars long, excel only retrieves the 1st 256 characters and chops off the rest. I believe this is an excel design constraint and i don't know why msoft made it this way...The data concerned is 'description' thats why it can be more than 256 characters

One of the solutions that i thought off is to actually save the data that the pass-through query returns into a local table, and save the data in memo form. I tested this with a test table that saved the data in 'Memo' form instead of 'Text' form and exported this table to excel, which took in all the data >256 chars.

However i have no idea how to automate this, i.e. implement it into my current code. I know i will have to do away with the docmd.openquery and add some code that creates the table and the fields in memo format.
and then launch that table with docmd.opentable.

so how do i do that? or if there are other alternatives, please let me know.
i hope i'm clear enough.

Thanks
Eric

 

[KenHigg]

Can you use the test table you created and use it as a temp holding table. Say run the query which imports into the temp table, run your reports, export to excel, or whatever else you need to do then delete all the records once you're done with that set of data.

???

 

[KeithG]

I would make an Append query from the passthrough query to append the records to the table with the correct data types and then export to excel. So instead on docmd.openquery "Passthrough Query Name",you could use docmd.qpenquery "Append Query Name".Then export the data to Excel.

 

[Cire]

hmm the append method is slow, i'm trying out a code to export to excel directly. but i got some questions. i'm going to use this code for all the forms that i have, so i can i put it in a module and call this module into all my forms using docmd.openmodule "module name"? as apparently it doesnt work

my module is like this:

Option Compare Database

Public Function Export_to_Excel()
On Error GoTo Err_Handler

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim lngMax As Long
Dim lngCount As Long

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



Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler


End Function

my form code:
Option Compare Database

Private Sub cmd_close_form_Click()
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name
'DoCmd.OpenForm "Welcome"
End Sub

Private Sub cmd_help_Click()
DoCmd.OpenForm "all_requests_help"
End Sub




Private Sub cmd_RunQuery_Click()
On Error GoTo Error_Handler


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim sqlstring As String
Dim strPath As String


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
Set rst = qdf.OpenRecordset(dbOpenForwardOnly)

strPath = "C:\Results.xls"
strPath = InputBox("Enter Excel Path " & _
"(e.g. " & strPath & ")", _
"Results", strPath)

If Not strPath Like "*.xls" Then
Exit Sub
End If

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

DoCmd.OpenModule "ExportExcel"

Set qdf = Nothing

End If


Exit_Handler:

If Not xlSheet Is Nothing Then
Set xlSheet = Nothing
End If

If Not xlBook Is Nothing Then
Set xlBook = Nothing
End If

If Not xlApp Is Nothing Then
xlApp.Quit
Set xlApp = Nothing
End If

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not qdf Is Nothing Then
Set qdf = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

exit sub

Error_Handler:
On Error Resume Next
DoCmd.Hourglass False
DoCmd.SetWarnings True
varReturnVal = SysCmd(acSysCmdClearStatus)

MsgBox "An error has occured in this application. " & _
"please contact the technical support person at yaer@chevrontexaco.com and " & _
"tell them this information:" & _
vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & _
Err.Description, _
Buttons:=vbCritical, Title:="SVD Query System"

Resume Error_Handler
Resume
End Sub

apparently it doesnt work, after i click the command button, nothing was exported nor file created. i've used my sql statement in an openquery method without any problems. and instead of any export completed msg or error, my vba just launches with no indication of where the error nor an error pop up...

 

[Pat Hartman]

Are you sure you need a pass-through query? Why not just an append query from one linked table to another?

 

[Cire]

Are you sure you need a pass-through query? Why not just an append query from one linked table to another?

Linked tables were used in the old query system and it resulted in very long processing times: more than 1 hour and causing frequent hanging of access thus i was tasked to develop a better solution.

rite now i'm trying a make-table query, table made from the pass-through query so i run that query then open the table and its working fine now. now i only need some code to delete that table when the form is closed and remind myself to compact the database everyday

edit: deleting is easy but is there anyway to put the compact database command into code and place it in my "exit" command button?

 

[Cire]

woo hoo did a search and found the code, going to test it now!!

 

[goppo]

in access in tools -> option -> tab general you have an option "Compact on close"