Use SQL to Import Data Directly Into Access

[AccessJB]

I have this code working. But I can only get the query to load the data into a listbox. Does anyone know how I can get it directly into a table(tblTest)? I have tried putting "Inset into tblTest *" at the beginning of the SQL string. It does not cause an error but it does not load the data into the table.

Thank you very much.

-------------------------------------------------------------------------------------------------------------------

[CODE=vb]Private Sub buttonLoad3_Cli ck()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim sSQL As String

Set dbs = CurrentDb
Set qdf = dbs.CreateQuery Def("qryTest")

strSelect = "SELECT state_code, xxx, year, qtr "
strFrom = "FROM production_db.d bo.sy_test sy_test "
strWhere = "WHERE (state_code='88 ') AND"
strWhere = strWhere & " (xxx='123456789 0') AND "
strWhere = strWhere & " (year=2007) AND "
strWhere = strWhere & " (qtr=2)"

sSQL = strSelect & strFrom & strWhere
qdf.SQL = sSQL

qdf.Connect = "ODBC;DSN=Sybas e System 11;UID=YYYY;PWD =password;srvr= production;Data base=production _db"
Me.listTest.Row Source = "qryTest"
Me.listTest.Req uery

DoCmd.DeleteObj ect acQuery, "qryTest"

End Sub [/CODE]

-------------------------------------------------------------------------------------------------------------------

[Rabbit]

Creating the query definition does not run the query. You can use a DoCmd.OpenQuery for that.

[Scott Price]

The DoCmd.RunSQL method is perhaps more appropriate when storing the SQL string in a variable.

Regards,
Scott

[AccessJB]

The DoCmd.RunSQL method is perhaps more appropriate when storing the SQL string in a variable.

Regards,
Scott

Scott,

I believe the problem with that method is that it can't see tblTest because it is in the target Access database but not in the source Sybase database. I can get it to work by running a make table query but I am trying to create as few querys as possible. I figured there was some way to make it work with createTableDef and recordsets. And my supervisor does not want me to link to ths Sybase tables. I am just trying to query the Sybase tables and dump the results into an Access table.

[Scott Price]

That does make a difference! Reading over your first post, I don't see anywhere that you mention exactly what you are trying to do! Please state the question as clearly as you can in the future.

The DAO method that you are using in your code isn't the best way to go with this kind of linking/importing. You'll need to use another method.

What prevents you from using Access' built-in import wizard?

Regards,
Scott