>I am using an Access database to store my VB data. It has, of course, several tables and queries. It is on a pc that does not have Access, so I cant go in and modify a table or query using Access. I have to use VB to accomplish this. My questions are.......
>
>1) To change the design on a query, cant I just change the query on MY pc, upload it to the user pc, and use VB to delete the old query, and add the new one?
>
>2) If I need to change a tables design, that has thousands of records, is it best to build a new DB, import the data from the old DB, then delete the original and rename the new to the old?
>
>And all of this must be done using VB6.
>
>Any examples would be greatly appreciated.
To update an existing query you can retrieve its SQL property to a textbox, modify the string and then assign it back to the property. You can probably create a little utility EXE just for this kind of things. The code would look something like:
Dim ThisDB As DAO.Database
Dim qDef As DAO.QueryDef
Dim sOrig As String
Set ThisDB = OpenDatabase(App.Path & "\myDB.mdb")
Set qDef = ThisDb.QueryDefs("myQuery")
MsgBox qDef.SQL, vbOKOnly + vbInformation, "Query Definition"
sOrig = qDef.SQL
qDef.SQL = "Select * From authors"
qDef.Close
ThisDB.Close
Set qDef = Nothing
Set ThisDB = Nothing
And it is always a good idea to make a backup of the database file anytime you will make changes to any db object, especially on tables with existing data.