Looping through DB to get tables
-
@TableName was set with a cursor in SQL server. You'd have to do the same thing in access : open a recordset and fetch all the tables name from the system tables (sorry but I don't have sample script with me).
-
Here is a full demo. MAKE UP BACKUP OF YOUR FILE before testing this, it WILL delete records if any of your tables have a Yes/No field named Inactive.
Sub LoopingDemo()
Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
Dim strSQL As String, bInactive As Boolean
'
On Error GoTo err_sub
'
Set db = CurrentDb
'
DoCmd.SetWarnings False
'
For Each tdf In db.TableDefs
bInactive = False
For Each fld In tdf.Fields
If fld.Name = "Inactive" Then
bInactive = True
Exit For
End If
Next fld
If bInactive Then
strSQL = "DELETE * FROM " & tdf.Name & " WHERE Inactive = True"
DoCmd.RunSQL strSQL
End If
Next tdf
'
Set tdf = Nothing
Set db = Nothing
exit_sub:
DoCmd.SetWarnings True
Exit Sub
'
err_sub:
MsgBox Err.Description, vbCritical, "Error # " & Err.Number
Resume exit_sub
'
End Sub
-
Thank you guys,
So that's how you replicate a T-SQL cursor in Access.
-
Sorry took so long to reply, been crazy busy.. anyways, I tried querying the system table but i got the error: Record(s) could not be read, no permissions on MSysObjects, i have also looked into using tableDefs, but i am not real sure how to open the database file (no opendatabase like vb6) and always any input is greatly appreciated!
-
Hello Zach, glad you're back with us. We kind went off topic for a while here, can you supply a few details, like:
1) Can you convert the files from Access 97 to a newer format?
2) How many files will you be processing at one time?
3) Is this a one-time import, or will you run this periodically?
4) What version of SQL Server are you using?
5) What is your development platform - VB, VB.NET, C#; and what version are you on?
We've all been guessing at a solution without actually knowing the available tools and requirements.
-
Yes, sorry I have not provided enough details (newbie mistake). Anyways answers to your questions:
1) I CAN convert when I open the 97 DB in access 07 but I do not have to. Either way when I try to open the MSysObject table it says I do not have permission to read that table.
2) I will be looping through the tables and then inserting, no set number of rows or columns.
3) As far as I know I will be doing this one time to populate my database.
4) SQL Server 2005 (full version not express)
5) VB.Net, I have some vb6 code that another programmer wrote(why re-invent the wheel?) as my guts of the code but with the reference to DAO everything looks good.
My only problem, as far as I can tell, is opening the DB and looping thru the tables. I appreciate your patience and thank you once again.
-
Just an update:
I have tried an alternate route of loading the schema into a datagrid and i can see the table names there but i do not know how to loop thru the cells to look for the ones that end with "Data" in the column [table_name]
Anyone know how to loop thru cell in a column on a datagridview??
-
If this is a one-time import, I would just open SSMS and create a new database, call it Access97 or whatever.
Then right-click on your new database, click Tasks ~ Import Data, select your data source as Microsoft Access, Browse for the File name, then click Next, at Choose a Destination make sure your new database is the destination, click Next, Next again, and then you'll see a list of all the Access tables and queries that exist in your mdb file. Check off the ones you want, then click Next, Next again, and then click Finish. You'll see a screen that shows how many rows were transferred from each table. Click Close.
Expand the tables node on your new database, you'll be able to work with them from there.
As far as looping through the tables and doing something with them, you can use the sp_MSforeachtable system stored procedure. For example, open a new query window, use the new database and run this:
sys.sp_MSforeachtable @command1="print '?'"
which will list all the tables in the Messages window.
What to do next depends on exactly what you're doing with the data.
-
Well I am only pulling from certain columns if there is data in them, and if there is data in them then I will insert that data into a name column and insert a "parent" integer into a number column (the parent column will be the one before the name column, the columns are numbered, cat1, cat2, cat3 and so on until cat10)
So whatever is in the cat2 column will go in the name column with a value of 1 in the parent column... I hope that makes sense.
I am using VB.Net to do my testing but I cannot figure out how to set my db variable to the actual database file.
I know in VB6 it was db = OpenDatabase(MyDB). But I have found no such function in .Net.
Here is some of the code to let you get an idea of what I am working with:
Dim
newdb As dao.Database
Dim table As String
Dim cat1 As String
Dim cat1_id As String
Dim cat2 As String
Dim rs As Recordset
Dim sql As String
Dim db As dao.database
Dim acc_connect As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\Documents and Settings\name\MyDB""")
Dim sql_connect As New SqlConnection("Data Source = jjccbox\Server; Initial Catalog = Business; Integrated Security=true;")
*************This is where my loop should start to go through the access tables that end with "Data"************
********** db = ? table = ? newdb = ? **********
' get the category1 data
sql = "select distinct category1 from " & table & " order by category1"
cat1_rs = db.OpenRecordset(sql, acc_connect)
If Not (cat1_rs.BOF And cat1_rs.EOF) Then
While Not cat1_rs.EOF ' for each category1
cat1 = cat1_rs.Fields(
"category1")
sql =
"select * from folders where [name]='" & cat1 & "'"
rs = newdb.OpenRecordset(sql, sql_connect)
If rs.BOF And rs.EOF Then
sql =
"insert into folders([name]) values('" & cat1 & "')"
newdb.Execute(sql, sql_connect)
End If
sql =
"select * from folders where [name]='" & cat1 & "'"
rs = newdb.OpenRecordset(sql, sql_connect)
cat1_id = rs.Fields(
"id")
' now look for category2 data for this record
sql =
"select distinct category2 from " & table & " where category1='" & cat1 & "' and category2<>''"
cat2_rs = db.OpenRecordset(sql, acc_connect)
If Not (cat2_rs.BOF And cat2_rs.EOF) Then
While Not cat2_rs.EOF
and as always thanks a million
-
Here is a little update of progress only problem now is the newdb variable being set to open sql:
Dim sql_connect As New SqlConnection("Data Source = jjccbox; Initial Catalog = Business; Integrated Security=true;")
Dim acc_connect As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""C:\Documents and Settings\name\MyDB""")
sql_connect.Open()
acc_connect.Open()
Dim dt As DataTable = acc_connect.GetSchema("tables")
GridView1.DataSource = dt
For Each r As DataGridViewTextBoxColumn In GridView1.Columns
If r.ToString.Contains("Data") Then
Dim connect As New dao.PrivDBEngine
db = connect.OpenDatabase(
"C:\Documents and Settings\name\databases\MyDB.mdb")
table = r.ToString
newdb = connect.OpenDatabase(" *****This is where opening SQL comes in **** ")
*****rest of code goes here *****
-
BTW, the gridview looping is incorrect
-
here a snippet i saved for looping thru access tables from an application:
Set AccessCN = New ADODB.Connection
AccessCN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Clients\someAccessDb.mdb;Persist Security Info=False"
AccessCN.Open
Set rs = New ADODB.Recordset
Set rs = AccessCN.OpenSchema(adSchemaTables)
'Set rs = AccessCN.OpenSchema(adSchemaColumns)
Do While Not rs.EOF
If rs("TABLE_TYPE").Value = "TABLE" Then
Debug.Print "TABLENAME >" & rs("TABLE_NAME").Value & "<"
For i = 0 To rs.Fields.Count - 1
Debug.Print rs.Fields(i).NAME & " : " & rs.Fields(i).Value
Next i
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
AccessCN.Close
Set AccessCN = Nothing
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I had to re-work my code and re-think my approach but I think i've got it, thanks all
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply