Using VBA to create a Table in Access database

[Cire via AccessMonster.com]

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? i hope i'm clear enough.

Thanks
Eric

 

[Cire via AccessMonster.com]

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? i hope i'm clear enough.

Thanks
Eric

or if there are other alternatives, please let me know.

thanks again
Eric

 

[Brian Wilson]

or if there are other alternatives, please let me know.

thanks again
Eric

Here's an alternative that uses an ADO recordset. It uses late binding to
avoid referencing issues:


Public Function ExportExcel(strPath As String) As Boolean

On Error GoTo Err_Handler

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim rst As Object
Dim strConn As String
Dim strSQL As String
Dim lngCount As Long
Dim lngMax As Long

strConn = "Provider=sqloledb;" & _
"Data Source=MyServer;" & _
"Initial Catalog=MyDatabase;" & _
"Integrated Security=SSPI;"

strSQL = "SELECT * FROM MyTable"

Set rst = CreateObject("ADODB.Recordset")

rst.Open strSQL, strConn

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 = "Export"
.UsedRange.ClearContents
.Range("A1").CopyFromRecordset rst
End With

lngMax = xlBook.Worksheets.Count

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

xlBook.SaveAs strPath

ExportExcel = True

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
If rst.State > adStateOpen Then
rst.Close
End If
Set rst = Nothing
End If

Exit Function

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

End Function

 

[Duncan Bachen]

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? i hope i'm clear enough.

Thanks
Eric

What you can do is that instead of running your query directly, is to
use your query as the source for your make table query.

CurrentDb.Execute "SELECT * INTO tblYourTempTable FROM [qryYourPassthrough]"

 

[Brian Wilson]

Slight error:

If rst.State > adStateOpen Then

should be

If rst.State > adStateClosed Then

 

[TC]

You shouldn't really test those values like that. What if the values
change sign, in the next version of Access?

Remember that in theory, the actual values of any or all symbolic
constants, can change from version to version. (Nt that they're likely
to - bu they can!) So you should really only test for equal, or not
equal.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[Brian Wilson]

You shouldn't really test those values like that. What if the values
change sign, in the next version of Access?

Remember that in theory, the actual values of any or all symbolic
constants, can change from version to version. (Nt that they're likely
to - bu they can!) So you should really only test for equal, or not
equal.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

That is true. Currently five states are defined:

adStateClosed=0
adStateConnecting=2
adStateExecuting=4
adStateFetching=8
adStateOpen=1

In the code I posted, what I meant to say was:
"Unless the recordset is closed, then try and close it"

As far as I can see the state could only ever be adStateClosed or
adStateOpen, because there are no asynchronous actions being carried out, so
I could have written:

If rst.State = adStateOpen Then

but there was that bit of doubt in my mind as to whether any of the other
states might apply - hence the greater than sign. I suppose I could have
written the following to re-assure myself that no other state applies

Select Case rst.State
Case adStateOpen: rst.Close
Case adStateClosed: 'Do nothing
Case Else: MsgBox "I wasn't expecting this"
End Select

You don't happen to know whether any other state would be possible, given
the code written, do you?

 

[TC]

You don't happen to know whether any other state would be possible, given
the code written, do you?

Sorry, I don't actually know much about the other states, & whether
they would occur or not in the specified code. Maybe someone else can
comment.

Of course, the other approach is the good ol' TC knock-em-senseless:

on error resume next
rst.close
on error goto 0



Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[Cire via AccessMonster.com]

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.

[quoted text clipped - 11 lines]

Thanks
Eric

What you can do is that instead of running your query directly, is to
use your query as the source for your make table query.

CurrentDb.Execute "SELECT * INTO tblYourTempTable FROM [qryYourPassthrough]"

oh hmm but the direct export to excel using ADO would be neat too.
i guess i got to try both methods and see which is better, in addition, if i
use the make table method, how do i set access to delete the data inside that
table upon exit? plus an automatic compact and repair database. As your might
have known, access can grow very large if no compacting is done and as far as
i know, this only can be done manually.

Which means the ado method would be neat, however i'm current using a dao
method, i.e. currentdb and querydefs, so is there a similar method for dao
that exports the data directly to excel and keeps all the data intact
regardless of the length?

 

[Cire via AccessMonster.com]

[quoted text clipped - 6 lines]

CurrentDb.Execute "SELECT * INTO tblYourTempTable FROM [qryYourPassthrough]"

oh hmm but the direct export to excel using ADO would be neat too.
i guess i got to try both methods and see which is better, in addition, if i
use the make table method, how do i set access to delete the data inside that
table upon exit? plus an automatic compact and repair database. As your might
have known, access can grow very large if no compacting is done and as far as
i know, this only can be done manually.

Which means the ado method would be neat, however i'm current using a dao
method, i.e. currentdb and querydefs, so is there a similar method for dao
that exports the data directly to excel and keeps all the data intact
regardless of the length?

in the function declaration, there is a strpath, which i assume to be the
path where the user will save to? but i will need to create a module that
does this 1st right?

sorry i'm totally not familiar with functions, been working with subs so far
and i'm fairly new to access. in fact i changed function to sub and put that
whole list of code with changes to the sqlstring and connection string, into
a private sub cmdExportToExcel_click but it didn't work lol. the error
pointed to the strpath. so how do i code this? i.e. prompting the user to
choose a directory to save to.

 

[Brian Wilson]

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.

[quoted text clipped - 6 lines]

CurrentDb.Execute "SELECT * INTO tblYourTempTable FROM
[qryYourPassthrough]"

oh hmm but the direct export to excel using ADO would be neat too.
i guess i got to try both methods and see which is better, in addition, if
i
use the make table method, how do i set access to delete the data inside
that
table upon exit? plus an automatic compact and repair database. As your
might
have known, access can grow very large if no compacting is done and as far
as
i know, this only can be done manually.

Which means the ado method would be neat, however i'm current using a dao
method, i.e. currentdb and querydefs, so is there a similar method for dao
that exports the data directly to excel and keeps all the data intact
regardless of the length?

in the function declaration, there is a strpath, which i assume to be the
path where the user will save to? but i will need to create a module that
does this 1st right?

sorry i'm totally not familiar with functions, been working with subs so
far
and i'm fairly new to access. in fact i changed function to sub and put
that
whole list of code with changes to the sqlstring and connection string,
into
a private sub cmdExportToExcel_click but it didn't work lol. the error
pointed to the strpath. so how do i code this? i.e. prompting the user to
choose a directory to save to.

You can, if you prefer, use the DAO object model instead.
You can change the function to a sub.
Here we assume you have a pass-through query saved in Access as
"qryMyPassThrough"
You can prompt the use for the Excel file path and here I have shown a basic
way to do it. If you need a standard Windows file open dialog, then you
will need to add more code. However, this is such a standard task and
posted so many millions of times, I will use the simple method so we stay
focussed on exporting to Excel without any truncation happening.

Let me know how you get on with it:



Private Sub cmdExportToExcel_Click

On Error GoTo Err_Handler

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

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

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("qryMyPassThrough")

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 = "Export"
.UsedRange.ClearContents
.Range("A1").CopyFromRecordset rst
End With

lngMax = xlBook.Worksheets.Count

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

xlBook.SaveAs strPath

MsgBox "Export Complete", vbInformation

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

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

End Sub

 

[Cire via AccessMonster.com]

[quoted text clipped - 13 lines]

that exports the data directly to excel and keeps all the data intact
regardless of the length?

in the function declaration, there is a strpath, which i assume to be the
path where the user will save to? but i will need to create a module that
does this 1st right?

sorry i'm totally not familiar with functions, been working with subs so far
and i'm fairly new to access. in fact i changed function to sub and put that
whole list of code with changes to the sqlstring and connection string, into
a private sub cmdExportToExcel_click but it didn't work lol. the error
pointed to the strpath. so how do i code this? i.e. prompting the user to
choose a directory to save to.

hmm i have to call the function from within my command button, do i use
application.run "function name"?
and i still cannot figure out how to code strpath, any ideas?

 

[Cire via AccessMonster.com]

[quoted text clipped - 12 lines]

pointed to the strpath. so how do i code this? i.e. prompting the user to
choose a directory to save to.

hmm i have to call the function from within my command button, do i use
application.run "function name"?
and i still cannot figure out how to code strpath, any ideas?

ah i saw ur post after i posted lol. thanks i have to give it a try, the
append to table method is too slow so hopefully this method will do the trick,
tks i'll test it out.

 

[Brian Wilson]

Hi TC
I always used to use this approach, but at the moment I have been avoiding
it. It is not that I worry about it being unreliable, rather that
unexpected errors should be brought to my attention so that I can find out
why they are happening. This should, so the theory goes, make me a better
coder.

So in this example, I think I should be more confident that since I have
tried to open the recordset in a synchronous fashion (rst.Open does not
complete until all the records are loaded) then at the clean-up section of
my code the recordset state can only ever be adStateOpen or adStateClosed.
So my amended routine would be:

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

and I would not expect that code to fail ...ever. However, if something
went wrong such as it not being possible to close the recordset despite
being currently open, I would be alerted to the fact. I could then post my
findings to the newsgroups and endlessly discuss the reasons instead of
getting on with the work I am supposed to be doing...

 

[Cire via AccessMonster.com]

[quoted text clipped - 34 lines]

pointed to the strpath. so how do i code this? i.e. prompting the user to
choose a directory to save to.

You can, if you prefer, use the DAO object model instead.
You can change the function to a sub.
Here we assume you have a pass-through query saved in Access as
"qryMyPassThrough"
You can prompt the use for the Excel file path and here I have shown a basic
way to do it. If you need a standard Windows file open dialog, then you
will need to add more code. However, this is such a standard task and
posted so many millions of times, I will use the simple method so we stay
focussed on exporting to Excel without any truncation happening.

Let me know how you get on with it:

Private Sub cmdExportToExcel_Click

On Error GoTo Err_Handler

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

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

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("qryMyPassThrough")

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 = "Export"
.UsedRange.ClearContents
.Range("A1").CopyFromRecordset rst
End With

lngMax = xlBook.Worksheets.Count

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

xlBook.SaveAs strPath

MsgBox "Export Complete", vbInformation

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

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

End Sub

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

before this statement is qdf.sql= sqlstring
sql string is my entire sql statement as i've allowed users earleir to enter
in data which forms the WHERE clause of the sql statement. thus after
gathering their data, i declared qdf.sql = sqlstring followed by
"Set rst = qdf.OpenRecordset(dbOpenForwardOnly)"
and i got that error. so i removed my sqlstring and tested it with a simple
ready built pass-through query and i got an error on ".Range("A2").
CopyFromRecordset rst"

But the funny thing is that before all these errors, i managed to export a
file with the intact records. however all the field names were missing, only
the data was exported not the field names. then when i tried to re-run again,
the above happened. hopefully u can shed some light on these probs, but at
least i'm getting nearer. thanks for all your help, i'll be leaving office so
i'll catch u tmr.

Thanks again
Eric

 

[Cire via AccessMonster.com]

[quoted text clipped - 110 lines]

End Sub

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

before this statement is qdf.sql= sqlstring
sql string is my entire sql statement as i've allowed users earleir to enter
in data which forms the WHERE clause of the sql statement. thus after
gathering their data, i declared qdf.sql = sqlstring followed by
"Set rst = qdf.OpenRecordset(dbOpenForwardOnly)"
and i got that error. so i removed my sqlstring and tested it with a simple
ready built pass-through query and i got an error on ".Range("A2").
CopyFromRecordset rst"

But the funny thing is that before all these errors, i managed to export a
file with the intact records. however all the field names were missing, only
the data was exported not the field names. then when i tried to re-run again,
the above happened. hopefully u can shed some light on these probs, but at
least i'm getting nearer. thanks for all your help, i'll be leaving office so
i'll catch u tmr.

Thanks again
Eric

wierd how did this message get on top of my later posts? maybe its a forumn
design..

 

[TC]

Hi TC
I always used to use this approach, but at the moment I have been avoiding
it. It is not that I worry about it being unreliable, rather that
unexpected errors should be brought to my attention so that I can find out
why they are happening. This should, so the theory goes, make me a better
coder.

Brian, I absolutely agree with that approach. The only way we all
improve, is by careful attention to very small details, IMHO.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[Brian Wilson]

Hey Brian, i've got a prob, access detected an error in this line:

"Set rst = qdf.OpenRecordset(dbOpenForwardOnly)"

before this statement is qdf.sql= sqlstring
sql string is my entire sql statement as i've allowed users earleir to
enter
in data which forms the WHERE clause of the sql statement. thus after
gathering their data, i declared qdf.sql = sqlstring followed by
"Set rst = qdf.OpenRecordset(dbOpenForwardOnly)"
and i got that error. so i removed my sqlstring and tested it with a
simple
ready built pass-through query and i got an error on ".Range("A2").
CopyFromRecordset rst"

But the funny thing is that before all these errors, i managed to export a
file with the intact records. however all the field names were missing,
only
the data was exported not the field names. then when i tried to re-run
again,
the above happened. hopefully u can shed some light on these probs, but at
least i'm getting nearer. thanks for all your help, i'll be leaving office
so
i'll catch u tmr.

Thanks again
Eric

The fact that the field names are not showing is to be expected.
CopyFromRecordset just copies the data. If you need to see the field names,
then you can simply loop through the fields, writing each field name in a
new cell:

With xlSheet
.Name = "Export"
.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


If you want to modify the code so that you pass in a simple SQL string, you
could use code like that shown below to redefine the pass-through query:

strConnect = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=MyServer;" & _
"Database=" & strDatabase & ";" & _
"Trusted_Connection=yes;"

strSQL = "SELECT ID, FieldOne, FieldTwo FROM " & _
"MyTable WHERE ID<10;"

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("qryMyPassThrough")

qdf.Connect = strConnect

qdf.SQL = strSQL



As to the other code failing, I can't think whay that might be. I know
there were bugs in previous versions of Office where null values in the
recordset could cause trouble. What version of Excel and Access are you
using?

 

[Cire via AccessMonster.com]

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

[quoted text clipped - 22 lines]

Thanks again
Eric

The fact that the field names are not showing is to be expected.
CopyFromRecordset just copies the data. If you need to see the field names,
then you can simply loop through the fields, writing each field name in a
new cell:

With xlSheet
.Name = "Export"
.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

If you want to modify the code so that you pass in a simple SQL string, you
could use code like that shown below to redefine the pass-through query:

strConnect = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=MyServer;" & _
"Database=" & strDatabase & ";" & _
"Trusted_Connection=yes;"

strSQL = "SELECT ID, FieldOne, FieldTwo FROM " & _
"MyTable WHERE ID<10;"

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("qryMyPassThrough")

qdf.Connect = strConnect

qdf.SQL = strSQL

As to the other code failing, I can't think whay that might be. I know
there were bugs in previous versions of Office where null values in the
recordset could cause trouble. What version of Excel and Access are you
using?

2003 for both, i just got back to office so i got to do more testing but i
sense i'm getting there

 

[Cire via AccessMonster.com]

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

[quoted text clipped - 22 lines]

Thanks again
Eric

The fact that the field names are not showing is to be expected.
CopyFromRecordset just copies the data. If you need to see the field names,
then you can simply loop through the fields, writing each field name in a
new cell:

With xlSheet
.Name = "Export"
.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

If you want to modify the code so that you pass in a simple SQL string, you
could use code like that shown below to redefine the pass-through query:

strConnect = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=MyServer;" & _
"Database=" & strDatabase & ";" & _
"Trusted_Connection=yes;"

strSQL = "SELECT ID, FieldOne, FieldTwo FROM " & _
"MyTable WHERE ID<10;"

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("qryMyPassThrough")

qdf.Connect = strConnect

qdf.SQL = strSQL

As to the other code failing, I can't think whay that might be. I know
there were bugs in previous versions of Office where null values in the
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