Looping through DB to get tables
-
I am new to this so please be patient, but my question is i use oledb to connect to access database and that works fine, my problem is that i need to loop through this database with all tables that end with the word Data. I can't figure out how to loop through the tables: angry: When i do though i will pull certain columns and then insert them into sql server, I just need to loop through some tables.. thanks for ANY help
-
Check out the adox refferences in access. That'll get you started.
You can also access the system objects to find the list of tables of Access (check the options to show those tables, then manually scan them ot figure the rest out).
-
i did a little research on the reference but i am now thinking that if I use the SSIS package and do a union all that it might solve my problem, i just dont know if SSIS can do a very large union all. I will post back when i get SSIS installed on my machine, i am open to any suggestions thanks again...
-
If your question is - how to loop thru the tables in Access...
Set a reference to the DAO object library, then use something like this:
Dim db As DAO.Database, tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If Right(tdf.Name,4) = "Data" Then
' your code goes here
End If
Next tdf
Set tdf = Nothing
Set db = Nothing
-
Please excuse my ignorance but I don't see how to add the DAO, I see only the ADODB reference.. and I think I forgot to mention these are access 97 DB's, thanks once again.
-
Maybe I misunderstood. Are you working from the Access side, or from the SQL Server side? In Access 97, you would open any module and then click Tools ~ References and then make sure that "Microsoft DAO 3.51 Object Library" is selected.
If you are working from the SQL side, you might want to create a linked server to the Access db. There is a system table in Access called MSysObjects, the Access table names are in the Name field, and the native tables would be Type = 1.
-
i know this is a little off but its from access 97 format (opens with access 07), goes into sql server 2005, and in vb.net code or SSIS i want to loop through an access DB and get certain info from columns in all table that end with the word data, i am sorry for any confusion and i appreciate your patience..
-
k i am an idiot.. i got the reference in vs2005
-
when i am linking the access db with Linked Servers in sql server it give me an error message could not find installable ISAM. i don't have a clue at to what direction to take...
thanks for any help -
I get the same error that you get - apparently you cannot link to an Access 97 db using the 4.0 provider that comes with SS 2005.
However, if you could convert the Access file to 2000 format, here's the answer to your original post. In this example, I have a 2000-format file named C:\BILL\Test2000.mdb
First, run this one-time to create the linked server:
sp_addlinkedserver
@server = 'Test2000',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = '',
@datasrc = 'C:\BILL\Test2000.mdb'
GO
sp_addlinkedsrvlogin
@rmtsrvname='Test2000',
@useself='False',
@rmtuser='Admin',
@rmtpassword=''
GO
Then, you can use this to build a temp table with the desired table names, and loop thru to view the data using a cursor and dynamic sql (shame on me):
CREATE TABLE #temp
(
TABLE_CAT varchar(MAX),
TABLE_SCHEM varchar(MAX),
TABLE_NAME varchar(MAX),
TABLE_TYPE varchar(MAX),
REMARKS varchar(MAX))
INSERT INTO #temp
EXEC sp_tables_ex 'Test2000','%Data'
DECLARE @tablename varchar(64)
DECLARE curTBL CURSOR FOR
SELECT TABLE_NAME FROM #temp
OPEN curTBL
FETCH NEXT FROM curTBL INTO
@tablename
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('SELECT * FROM Test2000...' + @tablename)
FETCH NEXT FROM curTBL INTO
@tablename
END
CLOSE curTBL
DEALLOCATE curTBL
DROP TABLE #temp
-
Hi William, what's dynamic sql and can you implement it using Jet sql as well?
-
Dynamic sql is when you build your sql statement as a string, and then execute it, like this in SQL Server:
EXEC ('SELECT * FROM Test2000...' + @tablename)
instead of using something like this:
SELECT * FROM Test2000...MyTablename
but in the example, we don't know the table name(s) ahead of time, so we use @tablename which allows us to loop thru & plug in the table names as we go along.
In Access, there are several ways of using dynamic sql. You start by building your sql statement as a string, here I'm calling it strSQL:
1. First, this works for INSERT, UPDATE, or DELETE
DoCmd.RunSql strSQL
2. Second, this works for SELECT, INSERT, UPDATE or DELETE
CurrentDb.QueryDefs("MyQuery").SQL = strSQL
3. Finally, this works for INSERT, UPDATE or DELETE:
Dim db As DAO.Database, qdf As DAO.QueryDef
Set db = CurrentDb
db.QueryDefs("MyQuery").SQL = strSQL
Set qdf = db.QueryDefs("MyQuery")
qdf.Execute
...the major difference is that when you use dynamic sql, the database engine doesn't have a chance to optimize the query execution, so it may tend to be slower than a saved T-SQL procedure or Access query. But it can be useful when you don't know all of the data to be processed ahead of time.
-
Thank you,
When you don't know the table(s) name(s), what's the equivalent in Access for
EXEC ('SELECT * FROM Test2000...' + @tablename)
?
-
If you want to run an action query (INSERT, UPDATE, or DELETE) then you could use something like this. This example uses a table named tblClient which has a Yes/No field named Inactive:
Dim strTableName As String, strSQL As String
strTableName = "tblClient"
strSQL = "DELETE * FROM " & strTableName & " WHERE Inactive = True"
DoCmd.RunSql strSQL
...which would delete any records from tblClient where the Inactive field is True.
However, that doesn't work if you want to SELECT records, because Access doesn't have a "Results" window like SQL Server.
If you want to SELECT records dynamically, you would have to first create a saved query, let's call it MyQuery; the actual sql statement doesn't matter, because we're going to replace it anyway, so you could simply create it as
SELECT 1 AS Test;
Now, let's just look at the Inactives:
Dim strTableName As String, strSQL As String
strTableName = "tblClient"
strSQL = "SELECT * FROM " & strTableName & " WHERE Inactive = True"
CurrentDb.QueryDefs("MyQuery").SQL = strSQL
DoCmd.OpenQuery "MyQuery"
..does that help?
-
Thank you William again,
In your previous post you say, "we use @tablename which allows us to loop thru & plug in the table names as we go along."
but what's the equivalent to @tablename in Access which would allow us to loop thru & plug in the table names as we go along?
I don't see any looping through table names in
strSQL = "SELECT * FROM " & strTableName & " WHERE Inactive = True"
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply