Re: Using VBA to create a Table in Access database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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?



.

Relevant Pages

  • Re: Using VBA to create a Table in Access database
    ... Hey Brian, i've got a prob, access detected an error in this line: ... "Set rst = qdf.OpenRecordset" ... in data which forms the WHERE clause of the sql statement. ... i declared qdf.sql = sqlstring followed by ...
    (microsoft.public.access.modulesdaovba)
  • Re: Scroll through records and get match
    ... "User-defined type not defined" at Dim dbs As DAO.Database. ... "Invalid argument" Set rst = dbs.OpenRecordset ... that I posted is a select query. ... cannot use it to open an SQL statement that you generate via code. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Fastest way to do this?
    ... Dim rst As Recordset ... Dim intCnt As Integer ... Set rst = CurrentDb.OpenRecordset ... I would think the large SQL statement would be slower because I ...
    (comp.databases.ms-access)
  • Re: Nested Datalists
    ... This SQL statement returns all the information that I need (Drawing #, ... (MaxOfDCN) ... this SQL string as the DataSource for one of my datalists. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: newbie - sql UPDATE statement
    ... You seemed to refer to the same ... "Newbee Adam" wrote in message ... > I do think a replace function can be used in an sql statement like I use ... > outside an sql string like: ...
    (microsoft.public.access.gettingstarted)