Showing all posts tagged: 'Microsoft Access'

When importing data from SQL Server using the ODBC connector in MS Access, your tables will end up prefixed with their corresponding schema. In most cases this will be the "dbo" schema. If you need to remove the prefix, these are the steps you need to follow: Open your MS Access file (mdb or accdb) If you are presented with a Security Warning, enable execution of secure content Navigate to Database Tools and from the top left corner press the Visual Basic button This opens a new window Add a new module Replace everything in this module with …[read more]

If you need to empty/truncate all user data from an MS Access database, the following VB script should do the job: Public Sub TruncateTables() On Error GoTo Error_TruncateTables Dim DB As DAO.Database Dim TDF As DAO.TableDef Dim strSQL_DELETE As String Set DB = CurrentDb() For Each TDF In DB.TableDefs If Left(TDF.Name, 4) <> "MSys" Then If Left(TDF.Name, 1) <> "~" Then strSQL_DELETE = "DELETE FROM " & TDF.Name & ";" DB.Execute strSQL_DELETE End If End …[read more]