I need to allow my user to select data from a previous DB and copy that data to a newly created DB. The set of data to be copied will be selected from user criteria
dim rs As RecordSet = gData.csbbData.SQLSelect("SELECT * FROM myTable WHERE teamName= '" + myTeam + "'")
This is the problem: there will be a number of “myTeams” depending on user choice. Do I need to create a separate recordset for each search? For each myTeam, there will also be 2 other Tables where records need to be selected and then written to the new DB. There must be a simpler way than creating all of those recordsets, but I’m not savvy enough with SQL to know what it is.
This blog by Geoff shows how to create an in memory sqllite database copying data from another SQLLite database on disk. I think it may have some relevance to what you’re trying to achieve.
Thank you, Wayne, for that link. I already have 2 DB’s, I don’t see how creating an in-memory DB will help. I would still have the issue of copying only certain bits of data by creating many, many recordsets. Maybe the shortcut to the multiple recordset approach which I’m seeking doesn’t exist.
AttachDatabase works with existing files as well. Then you can “create table from” or “insert into myTable select * from theAttachedDB.myTable where …”
Thanks, Tim. That looks to be an approach which will work. This is uncharted territory for me. I’ve been working for the last hour trying to make sense of the INSERT INTO command. Based upon your post, here is what I have so far…
[code]dim myNum As string = “1”
gData.csbbData.SQLExecute(“INSERT INTO Teams SELECT * FROM attachDB.Teams WHERE TeamNum = '” + myNum + “’”)
// Display Error If One Occurred
if gData.csbbData.Error then
I was able to successfully attach the “attachDB” to “gData.csbbData” Upon running the above I get the error “No such table exists attachDB.Teams”. Both the previous and the new DB’s have an already created table named “Teams” and each Teams Table has a TeamNum Field. Apparently, I am not referring to it correctly. Much obliged for any additional help.
Did you use
dim f as folderitem = ...
if gData.csbbData.AttachDatabase(f, "attachDB") then
to name the attached database?
If gData.csbbData.attachDatabase(prevFileLoc, attachDB) Then
Where prevFileLoc is a folderitem pointing to the secondary DB and attachDB is a string Var. containing the name of the secondary DB, I got the MsgBox.
Is the above a typo or does the attachDB variable contain the string “attachDB”?
Dim attachDB As String = “csData_2013.sqlite”
Then you are not using it correctly.
With your code, the SQL would need to be written as:
SELECT * FROM csData_2013.sqlite.Teams WHERE ...
The second parameter is a made up name to use to refer to the tables from the attached DB using SQL. You should probably just use something like “attachDB” so that your SQL will work. This is essentially what Tim said above:
Dim f As FolderItem = ...
If gData.csbbData.AttachDatabase(f, "attachDB") Then
Which allows you to use this SQL:
SELECT * FROM attachDB.Teams WHERE ...
Stupid forum erased my answer :
That is your problem
INSERT INTO Teams SELECT * FROM “+attachDB+”.Teams WHERE TeamNum = ‘" + myNum + "’"
Got it! Paul and Dave, your two answers with different approaches helped the dim light to go on in my head.
Thanks, Tim, Paul, and Dave.