Why an query in VB6 with SQL Server and SQLite does not work?

camsedfj 于 2023-04-19 发布在 SQL Server

关注(0)|答案(1)|浏览(124)

I have an app in VB6. The app works with dbs SQL Server and SQLite. I installed ODBC drivers for SQLite. I made connections to dbs cn and cnsqlite. The app has to filter data from SQL Server, create a new empty db SQLite and insert data into db SQLite. I create a query and a command

sqlStr2 = "Select * INTO " & rsad.Fields("Table_Name") & " FROM (select * from  [ODBC;Driver={SQL Server Native Client 10.0};Server=server2\SQLEXPRESS;Database=base1;uid=user;pwd=userpwd;].dbo." & rsad.Fields("Table_Name") & filter & ")"
cnsqlite.Execute sqlStr2, dbFailOnError

The app in the command arises the error only one SQL statement allowed

I deleted in the query (select * from). The same error arised. Before with db MS Access these statements normally worked. What to do? How rightly need to compose the query?

Temur Yakubov

I deleted in the query (select * from).

sql-server

来源：https://stackoverflow.com/questions/75995953/why-an-query-in-vb6-with-sql-server-and-sqlite-does-not-work

1条答案

按热度按时间

szqfcxe21#

Instead of select * into you need to use create table tablename as

sqlStr2 = "create table " & rsad.Fields("Table_Name") & " as select * FROM (select * from  [ODBC;Driver={SQL Server Native Client 10.0};Server=server2\SQLEXPRESS;Database=base1;uid=user;pwd=userpwd;].dbo." & rsad.Fields("Table_Name") & filter & ")"
cnsqlite.Execute sqlStr2, dbFailOnError

If above query is not working then you might try to create a temp table first then insert it into your desired table.

Dim sqlSubquery as String
sqlSubquery = "SELECT * FROM [ODBC;Driver={SQL Server Native Client 10.0};Server=server2\SQLEXPRESS;Database=base1;uid=user;pwd=userpwd;].dbo." & rsad.Fields("Table_Name") & filter
cnsqlite.Execute "CREATE TEMP TABLE temp_table AS " & sqlSubquery, dbFailOnError

'Select data from temporary table
Dim sqlMainQuery as String
sqlMainQuery = "create table " & rsad.Fields("Table_Name") &"

as select * from temp_table" cnsqlite.Execute sqlMainQuery, dbFailOnError

赞(0）回复(0）举报 2023-04-19

我来回答

Why an query in VB6 with SQL Server and SQLite does not work?

1条答案

相关问题

热门标签

最新问答