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.

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

Private Sub buttonLoad3_Click() 
Dim dbs As DAO.Database 
Dim qdf As DAO.QueryDef 
Dim sSQL As String 
 
Set dbs = CurrentDb 
Set qdf = dbs.CreateQueryDef("qryTest") 
 
 strSelect = "SELECT state_code, xxx, year, qtr " 
             strFrom = "FROM production_db.dbo.sy_test sy_test " 
             strWhere = "WHERE (state_code='88') AND" 
             strWhere = strWhere & " (xxx='1234567890') AND " 
             strWhere = strWhere & " (year=2007) AND " 
             strWhere = strWhere & " (qtr=2)" 
 
            sSQL = strSelect & strFrom & strWhere 
            qdf.SQL = sSQL 
 
         qdf.Connect = "ODBC;DSN=Sybase System 11;UID=YYYY;PWD=password;srvr=production;Database=production_db" 
         Me.listTest.RowSource = "qryTest" 
         Me.listTest.Requery                      
 
 DoCmd.DeleteObject acQuery, "qryTest" 
 
End Sub 

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

[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